sql server - Delimited Vertical Bar in SQL Table to another colum table

Question

I have table data textstring using separator '|'

I've tried using SSIS to export to excel file and import again using SSIS to remove "|" and add to each column. like this

But I was just wondering if any clue how to delimit to each column without export and import? Using query SQL?

Because my data rows more than 1000 I tried using this query.

 SELECT 
     REVERSE(PARSENAME(REPLACE(REVERSE(DataTextRaw), ',', '.'), 1)) AS [Num]
   , REVERSE(PARSENAME(REPLACE(REVERSE(DataTextRaw), ',', '.'), 2)) AS [Index]
   , REVERSE(PARSENAME(REPLACE(REVERSE(DataTextRaw), ',', '.'), 3)) AS [Value]
FROM dbo.RAW;

but because my data rows more than 1000 I think this query not okay.

question from:https://stackoverflow.com/questions/65952834/delimited-vertical-bar-in-sql-table-to-another-colum-table

Answer 1

You can use string_split()

select s.*
from t cross apply
     (select max(case when seqnum = 1 then value end) as num,
             max(case when seqnum = 2 then value end) as index,
             max(case when seqnum = 3 then value end) as year,
             max(case when seqnum = 4 then value end) as curr,
             max(case when seqnum = 5 then value end) as val
      from (select s.value,
                   row_number() over (order by charindex('|' + s.value + '|', '|' + t.datatextraw + '|') as seqnum
            from string_split(t.datatextraw, '|') s
           ) s
      ) s;

Rumor has it that you can use order by (select null) instead of the strange charindex() expression because the latest versions of SQL Server return the values in order. However, until the documentation changes, I won't recommend that approach.