MS SQL分割值（文本限定符+列determiter）

问题描述：

我的表是这样的：

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.

我需要表或选择这样

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.

我读How to fix the embedded text qualifier issue while exporting data to CSV flat file?

但我认为解决此问题的最佳选择是使用函数REPLACE（short_descrip “\”“，”\“\”“）

你有什么想法如何把真正的选择或更新？

谢谢。

说实话，我认为最好的办法是重新导入CSV和选择双引号文本限定符，所以它们不会在您的列中结束，而是运行查询来清理它们。 –

@Jacob HI试过我有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

答

您可以使用这样的查询：

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

类型在递归查询“rec_cte”的列“c”中的锚和递归部分之间不匹配。 Msg 240，Level 16，State 1，Line 1 类型在递归查询“rec_cte”的列“c1”中的锚和递归部分之间不匹配。 – Tester

我更正了脚本。你能显示table1的定义吗？ – xLiSPirit

答

按照类似的思路：

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分割值（文本限定符+列determiter）

相关推荐