用引号将引号之间的逗号替换为空格 [英] Replace comma between quotes with space

问题描述

您好，我的表格中的每一行都包含以下数据：

Hi I table in which every row have data like:

0150566115,"HEALTH 401K","IC,ON","ICON HEALTH 401K",,,1,08/21/2014

我想要的是删除双引号之间包含的每个逗号（，）。然后用逗号（，）丢弃字符串的其余部分

What i want is to remove every comma(,) that is enclosed between double quotes " ". and then slpit the rest of the string with comma(,)

我不想检查每个单字符设置标志是否包含双引号。

I dont want to do it checking every single character setting flags for start and end of double quotes.

我可以实现某种正则表达式吗？

Can i implement some sort of regex?

有没有简单的方法？

到目前为止，我尝试过的只是在逗号（，）的基础上分割字符串，但是它适用于单引号的双引号。

What i have tried so far is just to split the string on base of comma(,) But it is working for single block of double quotes.

    Declare @Query nvarchar(max) 

    Set @Query= 'Item1,Item2,"Item,Demo,3",New'

    Declare @start int, @len int
    SELECT @start = PATINDEX('%"%"%', @Query)+1

    print @start

    select @len=CHARINDEX('"', SUBSTRING(@Query, @start, LEN(@Query)))-1

    select 
        SUBSTRING(@Query, 1, @start - 2) +
        REPLACE((SUBSTRING(@Query, @start, @len)), ',', '') +
        SUBSTRING(@Query, @start + @len + 1, LEN(@Query))

推荐答案

尝试以下操作：

DECLARE @str nvarchar(max) = '0150566115,"HEALTH 401K","IC,ON","ICON HEALTH 401K",,,1,08/21/2014'
SELECT
  SUBSTRING(@str, 1, CHARINDEX('"', @str, 1) - 1)
  + REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING(@str, CHARINDEX('"', @str, 1), LEN(@str) - CHARINDEX('"', REVERSE(@str), 1) - CHARINDEX('"', @str, 1) + 2), ',', ' ' + CHAR(7) + ' '), CHAR(7) + ' ', ''), '" "', ','), '"', '')
  + REVERSE(SUBSTRING(REVERSE(@str), 1, CHARINDEX('"', REVERSE(@str), 1) - 1))


--Explaination
--Extracting the portion of the string before the first occurrence of '"'.
DECLARE @part1 nvarchar(max) = SUBSTRING(@str, 1, CHARINDEX('"', @str, 1) - 1)
SELECT
  @part1

--String between first and last occurrence of '"' and removing unwanted characters.
DECLARE @part2 nvarchar(max) = SUBSTRING(@str, CHARINDEX('"', @str, 1), LEN(@str) - CHARINDEX('"', REVERSE(@str), 1) - CHARINDEX('"', @str, 1) + 2)
SET @part2 = REPLACE(REPLACE(REPLACE(REPLACE(@part2, ',', ' ' + CHAR(7) + ' '), CHAR(7) + ' ', ''), '" "', ','), '"', '')
SELECT
  @part2

--String after the last occurrence of '"'
DECLARE @part3 nvarchar(max) = REVERSE(SUBSTRING(REVERSE(@str), 1, CHARINDEX('"', REVERSE(@str), 1) - 1))
SELECT
  @part3

--Concatenation
SELECT
  @part1 + @part2 + @part3

HTH !!!

这篇关于用引号将引号之间的逗号替换为空格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！