title: Split string and number columns
body: <p>Let's say I have a table such as</p>
  
  <pre><code>ItemID        ClassID
  ------------------------
  1             10, 13, 12
  2             5, 7
  </code></pre>
  
  <p>and would like to copy the data to another table like so</p>
  
  <pre><code>ItemID     Numbering       ClassID
  ----------------------------------
  1          1               10
  1          2               13
  1          3               12
  2          1               5
  2          2               7
  </code></pre>
  
  <ol>
  <li>Separating the comma-delimited <code>ClassID</code> field into individual rows, retaining the order they had in the first table</li>
  <li>Populating the <code>Numbering</code> row on insert. The <code>Numbering</code> column has sequential integers for each batch of <code>ClassID</code> and is why <code>ClassID</code> needs to be kept in order.</li>
  </ol>
  
  <p>I have attempted this with the following function:</p>
  
  <pre><code>  CREATE FUNCTION dbo.Split
     (
        @String NVARCHAR(MAX)
     )
    RETURNS @SplittedValues TABLE(
     Value INT
    )
   AS
   BEGIN
    DECLARE @SplitLength INT
    DECLARE @Delimiter VARCHAR(10) 
    SET @Delimiter = ','
  
    WHILE len(@String) &gt; 0
     BEGIN
      SELECT @SplitLength = (CASE charindex(@Delimiter, @String)
           WHEN 0 THEN
             datalength(@String) / 2
           ELSE
             charindex(@Delimiter, @String) - 1
         END)
  
     INSERT INTO @SplittedValues
     SELECT cast(substring(@String, 1, @SplitLength) AS INTEGER)
     WHERE
     ltrim(rtrim(isnull(substring(@String, 1, @SplitLength), ''))) &lt;&gt; '';
  
     SELECT @String = (CASE ((datalength(@String) / 2) - @SplitLength)
           WHEN 0 THEN
             ''
           ELSE
             right(@String, (datalength(@String) / 2) - @SplitLength - 1)
         END)
  
    END
  
   RETURN
  
  END
  </code></pre>
  
  <p>but it only partly works. It copies the rows the correct amount of times (i.e. three times for <code>ItemID=1</code>, and twice for <code>ItemID=2</code> in the above example), but they are exact copies of the row (all saying '10, 13, 12') and the comma-delimited parts are not split up. There is also nothing in the function to add to the <code>Numbering</code> column.</p>
  
  <p>So, I have two questions: How do I modify the above function to split up the <code>ClassID</code> string, and what do I add to correctly increment the <code>Numbering</code> column?</p>
  
  <p>Thanks!</p>