Split string and select into new table

Question

I have a table with a structure like this

ID pointCount   pointSeries
1 282   35.1079,-111.0151,35.1088,-111.0196...

Obviously the point series is string has pair of lat lon as points. I want to select the pointSeries into a new table

ID Lat Lon
1  35.1079 -111.0151
1  35.1088 -111.0196

What's the best way I can do a split and select into query?

I'd look at some of the string splitter functions here. There's a definition for a function widely regarded to be the best SQL-based split function. sqlservercentral.com/articles/Tally+Table/72993 — Xedni
– Xedni, Commented Mar 6, 2015 at 2:19

Felix Pamittan · Accepted Answer · 2015-03-06 02:28:44Z

4

You need to have a function for splitting comma-delimited strings into separate rows. Here is the DelimitedSplit8K function by Jeff Moden.

CREATE FUNCTION [dbo].[DelimitedSplit8K](
    @pString NVARCHAR(4000), @pDelimiter NCHAR(1)
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
WITH E1(N) AS (
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)
,E2(N) AS (SELECT 1 FROM E1 a, E1 b)
,E4(N) AS (SELECT 1 FROM E2 a, E2 b)
,cteTally(N) AS(
    SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
,cteStart(N1) AS(
    SELECT 1 UNION ALL 
    SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
),
cteLen(N1,L1) AS(
SELECT 
    s.N1,
    ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
FROM cteStart s
)
SELECT 
    ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
    Item       = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l

Then you need to pivot the result of the split to achieve the desired result:

;WITH CteSplitted AS(
    SELECT
        t.ID,
        x.ItemNumber,
        Item = CAST(x.Item AS NUMERIC(16,4)),
        RN = (ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ItemNumber) + 1) / 2
    FROM Test t
    CROSS APPLY dbo.DelimitedSplit8K(t.PointSeries, ',') x
)
SELECT 
    ID,
    Lat = MAX(CASE WHEN ItemNumber % 2 = 1 THEN Item END),
    Lon = MAX(CASE WHEN ItemNumber % 2 = 0 THEN Item END)
FROM CteSplitted
GROUP BY ID, RN

answered Mar 6, 2015 at 2:28

Felix Pamittan

31.9k7 gold badges43 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Seen Over a year ago

Thanks for the great example for the split function. It looks like there's a CROSS APPLY function in the CTE table. If my original table is huge I think the performance would be an issue. I am thinking another way to work around..

Felix Pamittan Over a year ago

See the article for the performance comparison. It's pretty fast.

Collectives™ on Stack Overflow

Split string and select into new table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related