title: SQL - STRING_SPLIT string position
body: <p>I have a table with two columns of comma-separated strings. The way the data is formatted, the number of comma-separated items in both columns is equal, and the first value in colA is related to the first value in colB, and so on. (It's obviously not a very good data format, but it's what I'm working with.)</p>
  
  <p>If I have the following row (PrimaryKeyID | column1 | column2):</p>
  
  <pre><code>1 | a,b,c | A,B,C
  </code></pre>
  
  <p>then in this data format, a &amp; 1 are logically related, b &amp; 2, etc.</p>
  
  <p>I want to use <code>STRING_SPLIT</code> to split these columns, but using it twice obviously crosses them with each other, resulting in a total of 9 rows.</p>
  
  <pre><code>1 | a | A
  1 | b | A    
  1 | c | A    
  1 | a | B    
  1 | b | B    
  1 | c | B    
  1 | a | C
  1 | b | C    
  1 | c | C
  </code></pre>
  
  <p>What I want is just the 3 "logically-related" columns</p>
  
  <pre><code>1 | a | A
  1 | b | B
  1 | c | C
  </code></pre>
  
  <p>However, <code>STRING_SPLIT(myCol,',')</code> doesn't appear to save the String Position anywhere.</p>
  
  <p>I have done the following:</p>
  
  <pre><code>SELECT tbl.ID,
        t1.Column1Value,
        t2.Column2Value
  FROM myTable tbl
  INNER JOIN (
     SELECT t.ID, 
         ss.value AS Column1Value, 
         ROW_NUMBER() OVER (PARTITION BY t.ID ORDER BY t.ID) as StringOrder
     FROM myTable t
     CROSS APPLY STRING_SPLIT(t.column1,',') ss
  ) t1 ON tbl.ID = t1.ID
  INNER JOIN (
     SELECT t.ID, 
         ss.value AS Column2Value, 
         ROW_NUMBER() OVER (PARTITION BY t.ID ORDER BY t.ID) as StringOrder
     FROM myTable t
     CROSS APPLY STRING_SPLIT(t.column2,',') ss
  ) t1 ON tbl.ID = t2.ID AND t1.StringOrder = t2.StringOrder
  </code></pre>
  
  <p>This appears to work on my small test set, but in my opinion there is no reason to expect it to work guaranteed every time. The <code>ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID)</code> is obviously a meaningless ordering, but it appears that, in absence of any real ordering, STRING_SPLIT is returning the values in the "default" order that they were already in. Is this "expected" behaviour? Can I count on this? Is there any other way of accomplishing what I'm attempting to do?</p>
  
  <p>Thanks.</p>
  
  <p>======================</p>
  
  <h1>EDIT</h1>
  
  <p>I got what I wanted (I think) with the following UDF. However it's pretty slow. Any suggestions?</p>
  
  <pre><code>CREATE FUNCTION fn.f_StringSplit(@string VARCHAR(MAX),@delimiter VARCHAR(1))
  RETURNS @r TABLE
  (
      Position INT,
      String VARCHAR(255)
  )
  AS
  BEGIN
  
      DECLARE @current_position INT
      SET @current_position = 1
  
      WHILE CHARINDEX(@delimiter,@string) &gt; 0 BEGIN
  
          INSERT INTO @r (Position,String) VALUES (@current_position, SUBSTRING(@string,1,CHARINDEX(@delimiter,@string) - 1))
  
          SET @current_position = @current_position + 1
          SET @string = SUBSTRING(@string,CHARINDEX(@delimiter,@string) + 1, LEN(@string) - CHARINDEX(@delimiter,@string))
  
      END
  
      --add the last one
      INSERT INTO @r (Position, String) VALUES(@current_position,@string)
  
      RETURN
  END
  </code></pre>