title: Subtraction of CHARINDEX in SUBSTRING is not working
body: <p>I have a value that I need to break down in column Name: <code>AB: ABC-ABCDE</code> I need the middle part which is <code>ABC</code>. I am using SUBSTRING and CHARINDEX to accomplish this but I am getting an error:</p>
  
  <pre><code>Msg 537, Level 16, State 2, Line 393
  Invalid length parameter passed to the LEFT or SUBSTRING function.
  </code></pre>
  
  <p>This happens when I subtract CHARINDEX to get the last value in SUBSTRING.
  Code:</p>
  
  <pre><code>SELECT PRODUCT = (SUBSTRING(Name, CHARINDEX(' ',Name)+1,CHARINDEX('-',Name)-(CHARINDEX(' ',Name)+1)))
  FROM A
  </code></pre>
  
  <p>What am I doing wrong?</p>
  
  <p>UPDATE: There is another value in the table like: <code>'ABC-ABC: ABCDEFG-ABCDEF GH'</code>. This gives negative value, hence why the error. Result should be <code>ABCDEFG</code></p>