SSIS - 删除字符 X 除非它后面跟着字符 Y [英] SSIS - remove character X unless it's followed by character Y

问题描述

假设我从文本文件导入了以下数据集:

Let's say I have the following dataset imported from a textfile:

Data
--------------------
1,"John Davis","Germany"
2,"Mike Johnson","Texas, USA"
3,"Bill "The man" Taylor","France"

我正在寻找一种方法来删除数据中的每个 "，除非它后面或前面有一个 ,.

I am looking for a way to remove every " in the data, unless it's followed or preceded by a ,.

所以在我的情况下，数据应该变成:

So in my case, the data should become:

Data
--------------------
1,"John Davis","Germany"
2,"Mike Johnson","Texas, USA"
3,"Bill The man Taylor","France"

我在 SSIS 中使用 import tekst 文件组件尝试了它，但是当我将列分隔符设置为 " 时会出错.如果我不设置分隔符，它会看到逗号Texas, USA"作为分割分隔符....

I tried it with the import tekst file component in SSIS, but that gives an error when I set the column delimiter to ". If I don't set a delimiter, it sees the comma in "Texas, USA" as a split delimiter....

有什么建议/想法吗?文本文件太大，无法为每一行手动更改它，因此这不是一个选项.

Any suggestions/ideas? The textfile is too large to change this manually for every line so that's not an option.

推荐答案

对最后一个 '"' 有点逃避，但是:

Bit of a cop-out on the last '"', but:

Create table #test ([Data] nvarchar(max))

insert into #test values ('1,"John Davis","Germany"'            )
insert into #test values ('2,"Mike Johnson","Texas, USA"'       )
insert into #test values ('3,"Bill "The man" Taylor","France"')

select replace(replace(replace(replace([Data],',"',',~'), '",','~,'),'"', ''),'~','"') + '"'
from #test

这篇关于SSIS - 删除字符 X 除非它后面跟着字符 Y的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！