CODEWITHSUNDEEP
sqlsql-servert-sql
Seen
Seen

Reputation: 4194

Split string and select into new table

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?

Upvotes: 0

Views: 239

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

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

Upvotes: 4

Related Questions