title: Splitting a Path Enumeration Model PathString in MySQL
body: <p>I'm trying to implement a Path Enumeration model as per Joe Celko's <a href="http://amzn.to/fmR7ex" rel="nofollow">book</a> (page 38).  The relevant attributes of my table (and the support table that just contains sequential integers) look like this:</p>
  
  <pre><code>Contribution
  ------------
  ContributionID
  PathString
  
  _IntegerSeries
  --------------
  IntegerID
  </code></pre>
  
  <p>_IntegerSeries contains integers 1 to n where n is bigger than I'll ever need.  Contribution contains three records:</p>
  
  <pre><code>1  1
  2  12
  3  123
  </code></pre>
  
  <p>... and I use a modified version of Joe's query:</p>
  
  <pre><code>SELECT SUBSTRING( c1.PathString
       FROM (s1.IntegerID * CHAR_LENGTH(c1.ContributionID))
       FOR CHAR_LENGTH(c1.ContributionID)) AS ContID
  FROM
   Contribution c1, _IntegerSeries s1
  WHERE
   c1.ContributionID = 3
   AND s1.IntegerID &lt;= CHAR_LENGTH(c1.PathString)/CHAR_LENGTH(c1.ContributionID);
  </code></pre>
  
  <p>... to successfully return a result set containing all of ContributionID 3's superiors in the hierarchy.  Now, in this example, the PathString column holds plain integer values and obviously we run into trouble once we hit ContributionID 10.  So we modify the PathString column to include separators:</p>
  
  <pre><code>1   1.
  2   1.2.
  3   1.2.3.
  </code></pre>
  
  <p>Now... the book doesn't give an example of getting superiors when the PathString uses delimiters... so I'll have to figure that out later.  But it does give an example for how to split up a PathString (which I'm guessing is going to help me do superior searches).  The MySQL version of the example code to do this is:</p>
  
  <pre><code>SELECT SUBSTRING( '.' || c1.PathString || '.'
       FROM s1.IntegerID + 1
       FOR LOCATE('.', '.' || c1.PathString || '.', s1.IntegerID + 1) - s1.IntegerID - 1) AS Node
  FROM _IntegerSeries s1, Contribution c1
  WHERE
   SUBSTRING('.' || c1.PathString || '.' FROM s1.IntegerID FOR 1) = '.'
   AND IntegerID &lt; CHAR_LENGTH('.' || c1.PathString || '.');
  </code></pre>
  
  <p>... but this code returns an empty result set.  I'm doing something wrong, but I'm not sure what.  Figured I'd put this out to the stackoverflow community prior to bothering Joe with an email.  Anyone have any thoughts?</p>
  
  <hr>
  
  <p>UPDATE</p>
  
  <hr>
  
  <p>Quassnoi's query... slightly modified a bit after testing, but exactly the same as his original functionally.  Very nice.  Much cleaner than what I was using.  Big thanks.</p>
  
  <pre><code>SET @contributionID = 3;
  
  SELECT  ca.*
  FROM
      Contribution c INNER JOIN _IntegerSeries s
          ON s.IntegerID &lt; @contributionID AND SUBSTRING_INDEX(c.PathString, '.', s.IntegerID) &lt;&gt; SUBSTRING_INDEX(c.PathString, '.', s.IntegerID + 1)
      INNER JOIN Contribution ca
          ON ca.PathString = CONCAT(SUBSTRING_INDEX(c.PathString, '.', s.IntegerID), '.')
  WHERE c.ContributionID = @contributionID;
  </code></pre>