MS SQL split value (text qualifier + column determiter)

Question

Good day

I have table like this:

Table1

463,"Prawn,Ging","NONE","22","22","Africa,Japan,China","01/01/1999 - 10/04/2017","NONE.
462,"GOLD,Fish","NONE","22","22","China","01/01/1999 - 10/04/2017","NONE.
461,"Long Dog","NONE","22","22","USA,France,Italy,Canada","01/01/1999 - 10/04/2017","NONE.
460,"Cat","NONE","22","22",,,,"NONE.

And I need table or select like this

Table1
Column1     Column2            Column3  Column3 Column4      Column5                       Column6              Column7
463         Prawn,Ging         NONE       22     22      Africa,Japan,China       01/01/1999 - 10/04/2017        NONE.
462         GOLD,Fish          NONE       22     22      China                    01/01/1999 - 10/04/2017        NONE.
461         Long Dog           NONE       22     22      USA,France,Italy,Canada  01/01/1999 - 10/04/2017        NONE.
460         Cat                NONE       22     22                                                              NONE.

I read How to fix the embedded text qualifier issue while exporting data to CSV flat file?

but I think best option to solve this issue is use funtion REPLACE(short_description,"\"","\"\"")

Do you have any idea how to put true select or update?

Thank you.

To be honest, I think the best option is to re-import your CSV and choose the double quotes as the text qualifier so they don't end up in your columns rather than running queries to clean them after. — Jacob H, Oct 05 '17 at 13:03
@Jacob H I Tried I have XY CSVs..http://help.pragmaticworks.com/dtsxchange/scr/FAQ%20-%20How%20to%20loop%20through%20files%20in%20a%20specified%20folder,%20load%20one%20by%20one%20and%20move%20to%20archive%20folder%20using%20SSIS.htm — Tester, Oct 05 '17 at 13:06

xLiSPirit · Answer 1 · 2017-10-05T16:16:02.190

You can use query like this:

if object_id('dbo.Table1') is not null
  drop table dbo.Table1
go
create table dbo.Table1(column1 nvarchar(max))

insert into Table1
select '463,"Prawn,Ging","NONE","22","22","Africa,Japan,China","01/01/1999 - 10/04/2017","NONE.'
union all select '462,"GOLD,Fish","NONE","22","22","China","01/01/1999 - 10/04/2017","NONE.'
union all select '461,"Long Dog","NONE","22","22","USA,France,Italy,Canada","01/01/1999 - 10/04/2017","NONE.'
union all select '460,"Cat","NONE","22","22",,,,"NONE.'

;with rec_cte as(
select substring(t1.column1, 1, t2.pos - 1) as id
     , substring(t1.column1, 1, t2.pos - 1) as c
     , replace(replace(substring(t1.column1, t2.pos + 2, 4000), ',,', ',"",'), ',,', ',"",') + '"' as c1
     , 1 as rn
  from Table1 t1
    cross apply (select charindex(',', t1.column1)) t2(pos)
union all
select t1.id
     , substring(t1.c1, 1, t2.pos - 1) as c
     , substring(t1.c1, t2.pos + 3, 4000) as c1
     , t1.rn + 1 as rn
  from rec_cte t1
    cross apply (select charindex('"', t1.c1)) t2(pos)
  where t2.pos > 0)

select [1] as colimn1
     , [2] as colimn2
     , [3] as colimn3
     , [4] as colimn4
     , [5] as colimn5
     , [6] as colimn6
     , [7] as colimn7
     , [8] as colimn8
from (
select id, c, rn
from rec_cte) src
pivot (max(c) for rn in ([1],[2],[3],[4],[5],[6],[7],[8])) as pvt

Types don't match between the anchor and the recursive part in column "c" of recursive query "rec_cte". Msg 240, Level 16, State 1, Line 1 Types don't match between the anchor and the recursive part in column "c1" of recursive query "rec_cte". — Tester, Oct 05 '17 at 14:44

score 0 · Answer 2 · answered Oct 05 '17 at 18:13

Along similar lines:

declare @tmp table
(
bigtext varchar(400)
);
INSERT INTO @tmp
VALUES
('463,"Prawn,Ging","NONE","22","22","Africa,Japan,China","01/01/1999 - 10/04/2017","NONE.'),
('462,"GOLD,Fish","NONE","22","22","China","01/01/1999 - 10/04/2017","NONE.'),
('461,"Long Dog","NONE","22","22","USA,France,Italy,Canada","01/01/1999 - 10/04/2017","NONE.'),
('460,"Cat","NONE","22","22",,,,"NONE.');

update @tmp SET bigtext = REPLACE(bigtext,',,','##');
update @tmp SET bigtext = REPLACE(bigtext,',"','#');
update @tmp SET bigtext = REPLACE(bigtext,'"','');

with cte( bigtext, c, position, single ) as (
select bigtext
     , STUFF( bigtext, 1, CHARINDEX('#', bigtext + ' #'), '') AS c
     , 1 AS position
     , convert(nvarchar(max),left(bigtext, CHARINDEX('#', bigtext + ' #') -1)) AS single
  from @tmp
 union all
select bigtext
     , STUFF(c, 1, CHARINDEX('#', c + ' #'), '')
     , position + 1
     , convert(nvarchar(max),left(c, CHARINDEX('#', c + ' #') -1))
  from cte
 where c > ''
)

SELECT pvt.bigtext
     , [1]
     , [2]
     , [3]
     , [4]
     , [5]
     , [6]
     , [7]
     , [8]
 FROM 
( SELECT bigtext
       , single
       , position
    FROM cte ) AS src
PIVOT
(
    MAX( single )
    FOR position IN ( [1], [2], [3], [4], [5], [6], [7], [8] )

) AS pvt;

MS SQL split value (text qualifier + column determiter)

2 Answers2