Split Delimited String into Columns in SQL Server

Question

I want to split a string into columns. After using attached code I am getting NULL data. Could someone help on this issue please?

CREATE TABLE #custAddress13(
   rowdata VARCHAR(max)
);

INSERT INTO #custAddress13(rowdata)
VALUES('13946005|13946005|10266|10266|CENTRAL FURNITURE & APPLIANCES, INC.|273|DAVID LUCE|01|000||||26 RIVER ST||SANFORD|ME|04073-9999|United States|2073245474|2074905182|')


SELECT 
  REVERSE(PARSENAME(REPLACE(REVERSE(RowDATA), '|', '.'), 1)) AS [L1]
    , REVERSE(PARSENAME(REPLACE(REVERSE(RowDATA), '|', '.'), 2)) AS [L2]
    , REVERSE(PARSENAME(REPLACE(REVERSE(RowDATA), '|', '.'), 3)) AS L3
    , REVERSE(PARSENAME(REPLACE(REVERSE(RowDATA), '|', '.'), 4)) AS L4
    , REVERSE(PARSENAME(REPLACE(REVERSE(RowDATA), '|', '.'), 5)) AS L5
    , REVERSE(PARSENAME(REPLACE(REVERSE(RowDATA), '|', '.'), 6)) AS L6
    , REVERSE(PARSENAME(REPLACE(REVERSE(RowDATA), '|', '.'), 7)) AS L7
    , REVERSE(PARSENAME(REPLACE(REVERSE(RowDATA), '|', '.'), 8)) AS L8
    , REVERSE(PARSENAME(REPLACE(REVERSE(RowDATA), '|', '.'), 9)) AS L9
    , REVERSE(PARSENAME(REPLACE(REVERSE(RowDATA), '|', '.'), 10)) AS L10
    , REVERSE(PARSENAME(REPLACE(REVERSE(RowDATA), '|', '.'), 11)) AS L11
    , REVERSE(PARSENAME(REPLACE(REVERSE(RowDATA), '|', '.'), 12)) AS L12
    , REVERSE(PARSENAME(REPLACE(REVERSE(RowDATA), '|', '.'), 13)) AS L13
    , REVERSE(PARSENAME(REPLACE(REVERSE(RowDATA), '|', '.'), 14)) AS L14
    , REVERSE(PARSENAME(REPLACE(REVERSE(RowDATA), '|', '.'), 15)) AS L15
    , REVERSE(PARSENAME(REPLACE(REVERSE(RowDATA), '|', '.'), 16)) AS L16
    , REVERSE(PARSENAME(REPLACE(REVERSE(RowDATA), '|', '.'), 17)) AS L17
    , REVERSE(PARSENAME(REPLACE(REVERSE(RowDATA), '|', '.'), 18)) AS L18
    , REVERSE(PARSENAME(REPLACE(REVERSE(RowDATA), '|', '.'), 19)) AS L19
    , REVERSE(PARSENAME(REPLACE(REVERSE(RowDATA), '|', '.'), 20)) AS L20
FROM #custAddress13;
GO

PARSENAME only goes up to 4 for the 2nd parameter, not 20. It's designed to split object names into parts. — Thom A
– Thom A ♦, Commented Mar 18, 2021 at 12:02
Ideally I would fix what ever process is passing the data and have it properly put them into columns to start with. — Thom A
– Thom A ♦, Commented Mar 18, 2021 at 12:03
If you're thinking STRING_SPLIT, @squillman , I'd be careful; it makes no promises it'll retain the ordinal positions of the values. — Thom A
– Thom A ♦, Commented Mar 18, 2021 at 12:06
A few years ago i had a similar problem, i used CLR user defined functions to implement a solution. I dont have access to the code anymore, but it was not complicated to write. — Menahem
– Menahem, Commented Mar 18, 2021 at 12:17

John Cappelletti · Accepted Answer · 2021-03-18 12:39:58Z

2

You can use a bit a JSON

Example

Select L1  = JSON_VALUE(S,'$[0]')
      ,L2  = JSON_VALUE(S,'$[1]')
      ,L3  = JSON_VALUE(S,'$[2]')
      ,L4  = JSON_VALUE(S,'$[3]')
      ,L5  = JSON_VALUE(S,'$[4]')
      ,L6  = JSON_VALUE(S,'$[5]')  --<< Expand to L20 / $[19]
From #custAddress13 A
Cross Apply ( values ( '["'+replace(replace(A.rowdata,'"','\"'),'|','","')+'"]' ) ) B(S)

or dbFiddle

answered Mar 18, 2021 at 12:39

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

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

1 Comment

Andrew Morton Over a year ago

It looks like there is a canonical question at How to split a comma-separated value to columns, but it lacks the via-JSON method you've shown here.

Collectives™ on Stack Overflow

Split Delimited String into Columns in SQL Server

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related