How to split a string on every 2nd delimiter? I have curves consisting of X/Y points (an array?) stored in a string with the same delimiter. There is 1 curve per row. Below is a simple example:

0,0,1,1,2,1.9,2.9,2.8,3.6,3.5


I want to extract each curve into a #table so I can edit them and ultimately put them back. 

X,Y
0,0
1,1
2,1.9
2.9,2.8
3.6,3.5


A curve could have anywhere from a dozen to hundreds of values, and the points may not be ordered.

What is the cleanest/simplest way of doing this?
I created a table value function (taken from here) to create an ordered split of your string.

CREATE FUNCTION dbo.SplitStrings
(
    @List       NVARCHAR(MAX),
    @Delimiter  NVARCHAR(255)
)
RETURNS @t TABLE([Index] INT IDENTITY(1,1), Item NVARCHAR(255))
AS
BEGIN
    INSERT @t(Item) SELECT SUBSTRING(@List, Number, 
        CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id])
        FROM sys.all_objects) AS n(Number)
    WHERE Number &lt;= CONVERT(INT, LEN(@List))
        AND SUBSTRING(@Delimiter + @List, Number, 1) = @Delimiter
    ORDER BY Number OPTION (MAXDOP 1);

    RETURN;
END
GO




I then used that TVF in the following code

Declare @X table (string varchar(50))
insert into @x (string) values('0,0,1,1,2,1.9,2.9,2.8,3.6,3.5')
SELECT s1.Item AS colx
    ,s2.item AS coly
FROM @x AS x
CROSS APPLY dbo.SplitStrings(x.string, ',') AS s1
CROSS APPLY dbo.SplitStrings(x.string, ',') AS s2
WHERE s2.[Index] = s1.[Index] + 1
    AND s1.[index] % 2 = 1
    AND s2.[Index] % 2 = 0




The code passes in your string into SplitString TVF using CROSS APPLY.  We use TWO CROSS APPLY's.  One for column X (alias s1) and one for column Y (alias s2).

Each table (s1 and s2) have the exact same rows ordered.  I'm basically using the logic that you commented on in your question. 


  Hmmm could i split on each value and use use odd/even row numbers to split into x,y?


We know the X sequence values are odd and the Y sequence values are even.  The logic is making sure the s2 index (which represents the Y-column) is +1 from the s1 row (which represents the X-column).

WHERE s2.[Index] = s1.[Index] + 1

We want to make sure the s1 rows are only the odd ones by using the modulus     operator (%).  If the remainder of the s1.index / 2 = 1, we have an odd row

AND s1.[index] % 2 = 1


We want to make sure the s2 rows are only the even ones by using the modulus operator (%).  If the remainder of the s1.index / 2 = 2, we have an even row

AND s1.[index] % 2 = 0




It seems to work on your sample data.

| colx | coly |
|------|------|
| 0    | 0    |
| 1    | 1    |
| 2    | 1.9  |
| 2.9  | 2.8  |
| 3.6  | 3.5  |