How to split a string on every 2nd delimiter?

Question

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?

• Sir Swears-a-lot: Hmmm could i split on each value and use use odd/even row numbers to split into x,y?
• McNets: Please tag your SQL Server version
• Sir Swears-a-lot: Here's a real example: 990,44,1010,86,1030,140,1050,202,1100,392,1150,662,1200,1088,1300,2400,1400,4200,1500,6300,1600,8700,1700,11400,1800,14450,1900,17800,2000,21750,2250,33350,2500,46100,2750,59900,3000,74750,3500,109500,

Answer 1

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 <= 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 |

• Sir Swears-a-lot: I'm curious about this piece of syntax: "AND s1.[index] % 2 = 1" what is it doing?
• Scott Hodgin - Retired: @Peter - I will update my answer tomorrow with more explanation of the code. It's quitting time for the day for me :)
• Sir Swears-a-lot: Ah. modulus. I'm terrible at math and even worse at explaining sql functions using the proper terminology. I had a rough idea it was something to do with that, but at first glance the code looked to me like something that would throw an error! Thanks again for your answer and explanation. :)

Answer 2

Since you are running sql server 2016, you can actually use the new string_split function, and here is a much simpler version using your real data in your comment

DECLARE @num varchar(max) ='990,44,1010,86,1030,140,1050,202,1100,392,1150,662,1200,1088,1300,2400,1400,4200,1500,6300,1600,8700,1700,11400,1800,14450,1900,17800,2000,21750,2250,33350,2500,46100,2750,59900,3000,74750,3500,109500';
; with c as (
 select value from string_split(@num, ',') 
 )
, c2 as (
 select value, rn=ROW_NUMBER() over (order by (select null)) 
 from c
 ) 
select X=value, t.Y from c2 as nc
cross apply (select value from c2 where nc.rn = c2.rn-1) T(y) 
where rn%2=1

The result is as follows

enter image description here

• Solomon Rutzky: I think this can be simplified by removing the c CTE and moving STRING_SPLIT into c2 as follows: ; with c2 as ( select value, rn=ROW_NUMBER() over (order by (select 0)) from string_split(@num, ',') ) . Not sure if there is any potential for ordering issues when doing that. Will assume not for the moment since it "works on my box" ;-)
• jyao: I totally agree, @SolomonRutzky ! You are my CLR hero. :-)