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.