How to separate values (delimiter separated) as different columns from SQL Server table having multiple rows?

Question

I have a SQL Server table which has multiple rows as below. Each row has one delimiter '^'. I want to make a separate column from each row.

Let's say this is the original table:

Sourcetable (only one column)

StringVal
-------------------------------------------------
57^H:\ ^ 200^Test ^2018-09-19 08:20:01.000
8^T:\ ^ 88^Test1 ^2018-09-1 08:00:01.000
33^D:\ ^ 40^Test2 ^2018-10-1 08:10:01.000

My request is to select columns as below in output by using above table

DestinationTable (with 5 columns)

FreeSpace | Total    | Drive | Server | Date
----------+----------+-------+--------+--------------------------
 57       |   200    | H:\   |  Test  | 2018-09-19 08:20:01.000
  8       |    88    | T:\   |  Test1 | 2018-09-1 08:00:01.000
 33       |    40    | D:\   |  Test2 | 2018-10-1 08:10:01.000

Note: String from source table also contains null values. Also that string contains many blank places within it without any order. So that also need to be handled.

I have tried to use string function but its giving me values for only up to the first delimiter and skipping further.

SELECT
    Substring(string, 1, Charindex('^', string) - 1) as Name,
    Substring(string, 4, Charindex('^', n) + 3) as Name1
FROM
    Sourcetable

I expect output as below with 5 different columns

FreeSpace | Total    | Drive | Server | Date
----------+----------+-------+--------+--------------------------
 57       |   200    | H:\   |  Test  | 2018-09-19 08:20:01.000
  8       |    88    | T:\   |  Test1 | 2018-09-1 08:00:01.000
 33       |    40    | D:\   |  Test2 | 2018-10-1 08:10:01.000

Possible duplicate of [How to split a comma-separated value to columns](https://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns) — sticky bit, Jan 05 '19 at 07:25

score 1 · Answer 1 · answered Jan 05 '19 at 07:30

You should dump your current single column table to a text file, and then reimport using SQL Server's import wizard. The delimiter you want to use is:

\s*^\s*

If the wizard does not accept this delimiter, then you might have to preprocess your file. You may do a regex replacement of \s*^\s* and replace with just comma. Then, import to SQL Server via the wizard, using comma as a separator.

score 1 · Answer 2 · answered Jan 05 '19 at 08:11

1

How about

WITH CTE AS
(
SELECT *
FROM Strings S CROSS APPLY
    (
      SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) RN,
             Value
      FROM STRING_SPLIT(Str, '^')
    ) SP
)
SELECT ID,
       Str,
       MAX(CASE WHEN RN = 1 THEN Value END) FreeSpace,
       MAX(CASE WHEN RN = 2 THEN Value END) DriveLetter,
       MAX(CASE WHEN RN = 3 THEN Value END) Total,
       MAX(CASE WHEN RN = 4 THEN Value END) Server,
       MAX(CASE WHEN RN = 5 THEN Value END) [Date]
FROM CTE
GROUP BY ID,
         Str;

Demo

answered Jan 05 '19 at 08:11

Ilyes

14,640
4
29
55

The separator isn't just `^`, sometimes it is `^` surrounded by whitespace. And `STRING_SPLIT` does not guarantee any order of what appears in the resulting set. You could partially get around this by trimming whitespace on each split term. – Tim Biegeleisen Jan 05 '19 at 10:06
Hello , Unfortunately I am using SQL 2014 and STRING_SPLIT function not there – Sanky Jan 11 '19 at 09:03
@SanketChakane You don't specify the version, but it's not a problem since [there is other ways](https://sqlperformance.com/2012/07/t-sql-queries/split-strings). Just create your own STRING_SPLIT ;) – Ilyes Jan 11 '19 at 09:15

score 0 · Accepted Answer · answered Jan 05 '19 at 17:04

My Input

select * from StringVal

StringVal
57^H:\ ^ 200^Test ^2018-09-19 08:20:01.000
8^T:\ ^ 88^Test1 ^2018-09-1 08:00:01.000
33^D:\ ^ 40^Test2 ^2018-10-1 08:10:01.000

Query

;with cte 
as (
 select 
   CONVERT (varchar (255), StringVal) StringVal
 , convert (varchar (255), StringVal) want -- 'want' means wanted column.
 , ROW_NUMBER () over (partition by StringVal order by (select null)) id  
 -- Row id. Based on original value. for track/count the '^'.
 from StringVal
 union all
 select 
   CONVERT (varchar (255), StringVal) 
 , convert ( varchar (255)
  , stuff (
     want, CHARINDEX ('^', want),1
     ,choose (id, '</FreeSpace ><Drive>','</Drive><Total>', '</Total><Server>','</Server><Date>')
     -- Replace the '^' by XML tags based on column's order.
  )
 ) 
 , id + 1
 from cte
 where want like '%^%'
)
select 
  FreeSpace.value('.', 'varchar (255)') FreeSpace
, Total.value('.', 'varchar (255)') Total
, Drive.value('.', 'varchar (255)') Drive
, Server.value('.', 'varchar (255)') Server
, Date.value('.', 'varchar (255)') Date
from (
 select convert(xml, '<StringVal><FreeSpace>' + want + '</Date></StringVal>') StringVal 
 from cte where id = 5
) xml
cross apply -- I'm not good in XML. so I need lot xml.nodes.
xml.StringVal.nodes('/StringVal/FreeSpace') FreeSpace(FreeSpace)
cross apply 
xml.StringVal.nodes('/StringVal/Total') Total(Total)
cross apply 
xml.StringVal.nodes('/StringVal/Drive') Drive(Drive)
cross apply 
xml.StringVal.nodes('/StringVal/Server') Server(Server)
cross apply 
xml.StringVal.nodes('/StringVal/Date') Date(Date)

Final Output

FreeSpace   Total   Drive   Server  Date
8           88      T:\     Test1   2018-09-1 08:00:01.000
57          200     H:\     Test    2018-09-19 08:20:01.000
33          40      D:\     Test2   2018-10-1 08:10:01.000

How to separate values (delimiter separated) as different columns from SQL Server table having multiple rows?

3 Answers3