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?

Is there a simple way?

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))

Solution 1:

Try the following:

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!!!

Solution 2:

The format of your data appears to be a delimited CSV format. This is a format frequently used by Excel, and it is unfortunate that SQL Server doesn't seem to have a simple way to read it in. When I am faced with such files, I usually do the following:

• Load them into Excel
• Save them in a tab delimited format
• Import them into SQL Server

Fortunately, when I've had to deal with such files, they have been on the small side and fit into Excel.

You seem to already have the data in the database. With a little research, I stumbled across this reference to split functions that take a string delimiter as well as a separator.