1

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
Zhorov
  • 28,486
  • 6
  • 27
  • 52
Sunil Singh
  • 31
  • 1
  • 3
  • 1
    `PARSENAME` only goes up to `4` for the 2nd parameter, not `20`. It's designed to split object names into parts. – Thom A Mar 18 '21 at 12:02
  • 2
    Ideally I would fix what ever process is passing the data and have it properly put them into columns to start with. – Thom A Mar 18 '21 at 12:03
  • What version of SQL Server? – squillman Mar 18 '21 at 12:04
  • 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 Mar 18 '21 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 Mar 18 '21 at 12:17
  • Microsoft SQL Server 2017 (RTM-CU22-GDR) (KB4583457) - 14.0.3370.1 (X64) Sql version – Sunil Singh Mar 18 '21 at 12:33
  • It looks to me like [this answer](https://stackoverflow.com/a/15108499/1115360) in [How to split a comma-separated value to columns](https://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns) would be a suitable solution. – Andrew Morton Mar 18 '21 at 12:40
  • The PARSENAME function works because the function has the ability to return a value from a specified position in a "Dot" delimited string. It does have a limitation that it will not work with more than four (4) delimited values within a string. – cristiandatum Aug 22 '22 at 09:19

1 Answers1

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

John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
  • It looks like there is a canonical question at [How to split a comma-separated value to columns](https://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns), but it lacks the via-JSON method you've shown here. – Andrew Morton Mar 18 '21 at 12:42