title: SQL Join Invalid length parameter passed to the LEFT or SUBSTRING function
body: <p>My database table looks like this:</p>
  
  <pre><code>ID  Value
  -------------
  1   a1¦aa¦a2
  1   b1¦aa¦b2
  1   b3¦tt¦b3
  2   a2¦aa¦z1
  2   b2¦tt¦z2
  2   b3¦tt¦z3
  </code></pre>
  
  <p>I'm trying to split each value up with ¦ as the break point which works:</p>
  
  <pre><code>Select 
      SUBSTRING(MT.Value, 1, CHARINDEX ( '¦' ,MT.Value) - 1) [part1],
      SUBSTRING(MT.Value, CHARINDEX ( '¦' ,MT.Value) + 1, 2) [part2],
      RIGHT(MT.Value,CHARINDEX('¦',REVERSE(MT.Value),0)-1) [part3]
  from 
      mytable MT
  where 
      MT.id = 1
  </code></pre>
  
  <p>But then I try to include a join to match up the records with id2 to id1 where the third part matches, SQL Server throws an error: </p>
  
  <blockquote>
    <p>Invalid length parameter passed to the LEFT or SUBSTRING function.</p>
  </blockquote>
  
  <p>Code:</p>
  
  <pre><code>Select 
      SUBSTRING(MT.Value, 1, CHARINDEX ( '¦' ,MT.Value) - 1) [part1],
      SUBSTRING(MT.Value, CHARINDEX ( '¦' ,MT.Value) + 1, 2) [part2],
      RIGHT(MT.Value, CHARINDEX('¦', REVERSE(MT.Value), 0) - 1) [part3],  
      mt1.value
  Join 
      mytable mt1 on mt1.ID = 2
                  and RIGHT(mt.Value, CHARINDEX('¦', REVERSE(mt.Value), 0) - 1) = SUBSTRING(mt1.Value, 1, CHARINDEX ( '¦' , mt1.Value) - 1)
  where 
      MT.id = 1
  from 
      mytable MT
  </code></pre>
  
  <p>Has anyone got any idea how else I can do it or where I'm going wrong?</p>