Remove a character from a records enclosed between double quotes

0 votes

2 answers

7953 views

sql-server t-sql regular-expression string-splitting

                          I have a 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 split the rest of the string with comma (,)

I don't 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 based on the comma (,) but it is also splitting values inside the quotes.

This is serving the purpose: how can I implement this in complete table (currently it is working if I have only one instance of double quotes block)?

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

This is the function I am using to split

    ALTER FUNCTION [dbo].[fnSplit](
        @sInputList VARCHAR(8000) -- List of delimited items
      , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
    ) RETURNS @List TABLE (id int, item VARCHAR(8000))
    
    BEGIN
    DECLARE @sItem VARCHAR(8000)
    Declare @ID as int
    Set @ID=0
    WHILE CHARINDEX(@sDelimiter,@sInputList,0)  0
     BEGIN
     SELECT
      @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
      @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
     Set @ID=@ID+1
     IF LEN(@sItem) > 0
      INSERT INTO @List SELECT @ID,@sItem
     END
    
    IF LEN(@sInputList) > 0
     INSERT INTO @List SELECT @ID,@sInputList -- Put the last item in
    RETURN
    END

The problem is that this is an application in MS Access, which narrows down my possibilities.  All I can do is passing the name of the file which is going to get imported by SQL Server. 
 
                        

Asked by Deep Sharma (127 rep)

Sep 6, 2014, 10:23 AM
Last activity: May 7, 2020, 09:42 PM

Remove a character from a records enclosed between double quotes

Related Questions