1

I have table data textstring using separator '|'

enter image description here

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

enter image description here

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.

Dale K
  • 25,246
  • 15
  • 42
  • 71
kucluk
  • 540
  • 2
  • 9
  • 29
  • 2
    The best thing is to start storing your data correctly, instead of delimited - that defeats the purpose of a relational database. BTW 1000 rows is *nothing* to SQL Server... – Dale K Jan 29 '21 at 10:40
  • Well [parsename()](https://learn.microsoft.com/en-us/sql/t-sql/functions/parsename-transact-sql) seems like a bit of a hack and is limited to four segments (three pipe delimiters in your case). If you're on a modern version of SQL Server you could probably use [string_split()](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql) and [pivot](https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot) the resulting rows into the required columns. – AlwaysLearning Jan 29 '21 at 11:44
  • 1
    One could be curious why, when you were originally importing this data in from a file, you didn't use appropriate field delimiters to split it into columns at that time. – AlwaysLearning Jan 29 '21 at 11:45
  • @alwaysLearning i got this data from read only table, so i dont have any access to file, i need to export data to excel using ssis and import again. – kucluk Jan 30 '21 at 13:35

1 Answers1

0

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Seem like that charindex could mix up the desired row_number effect if values from two columns are the same but are a few columns apart. – vhoang Jan 29 '21 at 19:38
  • @vhoang . . . Sadly, that is true. SQL Server does not have a documented solution for that, unless some other mechanism is used. As I mention in the answer, an *undocumented* feature is that you can use `order by (select null)`. But that is **undocumented**. – Gordon Linoff Jan 29 '21 at 19:44