Splitting string of values to table

,

This function can convert string with separated values to table.

Exclusive feature: items inside quotes will not be splitted!

Now you can easily perform joins on CSV strings!

For expample:

fn_split('1, 2, ''3, 4'', 5',',')=
1
2
3, 4
5

First parameter - string with values, second - delimiter character.

CREATE  function fn_split (@sText varchar(8000), @sDelim varchar(20) = ',')
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))
AS
BEGIN
DECLARE @a varchar(8000), @joined varchar(8000), @delimiter varchar(20)
Declare @idx smallint, @value varchar(8000), @bcontinue bit, @iStrike smallint, @iDelimLen tinyint
Set @sText = @sText+ ','''''
Set @delimiter = @sDelim
Set @sDelim = ''''

Set @idx = 0
Set @sText = ltrim(rtrim(@sText))
Set @iDelimLen = DATALENGTH(@sDelim)
Set @bcontinue = 1

If not (@iDelimLen=0)
begin
	while @bcontinue =1
	begin
		If charindex(@sDelim,@sText)>0
		begin
			Set @value=Substring(@sText,1,Charindex(@sDelim,@sText)-1)
			Insert @retArray values(@idx,@value)
			Set @iStrike = Datalength(@value)+@iDelimLen
			Set @idx = @idx + 1
--			Set @sText = Ltrim(right(@sText,Datalength(@sText)-@iStrike))
			Set @sText = right(@sText,Datalength(@sText)-@iStrike)
		end
		else
		begin
			Set @value = @sText
			Insert @retArray values(@idx,@value)
			Set @bcontinue = 0
		end
	end
end
else
begin
	while @bcontinue =1
	begin	
		If Datalength(@sText)>1
		begin
			Set @value = Substring(@sText,1,1)
			Insert @retArray values(@idx,@value)
			Set @idx = @idx + 1
			Set @sText = Substring(@sText,2,Datalength(@sText)-1)
		end
		else
		begin
			Insert @retArray values(@idx,@value)
			Set @bcontinue = 0
		end
	end
end

--REPLACE
Declare @id smallint
DECLARE a CURSOR READ_ONLY FOR select idx, value from @retArray
OPEN a
FETCH NEXT FROM a INTO @id, @a
WHILE (@@fetch_status <> -1)
BEGIN
	if (substring(@a,1,1)= @delimiter) or (substring(@a,datalength(@a),1)= @delimiter)
	update @retArray set value = replace (@a,@delimiter,'#^#') where idx = @id
	FETCH NEXT FROM a INTO @id, @a
END
CLOSE a
DEALLOCATE a

SET @Joined = ''
DECLARE a CURSOR READ_ONLY FOR select value from @retArray
OPEN a
FETCH NEXT FROM a INTO @a
WHILE (@@fetch_status <> -1)
BEGIN
    SET @Joined = @Joined + @a + '#^#'
    FETCH NEXT FROM a INTO @a
END
CLOSE a
DEALLOCATE a

Set @idx = 0
Set @sText = ltrim(rtrim(@joined))
Set @iDelimLen = DATALENGTH('#^#')
Set @bcontinue = 1
--Set @sDelim = @delimiter
Set @sDelim = '#^#'

delete from @retArray
If not (@iDelimLen=0)
begin
	while @bcontinue =1
	begin
		If charindex(@sDelim,@sText)>0
		begin
			Set @value=Substring(@sText,1,Charindex(@sDelim,@sText)-1)
			if '>'+@value+'<' <> '><' Insert @retArray values(@idx,@value)
			Set @iStrike = Datalength(@value)+@iDelimLen
			Set @idx = @idx + 1
--			Set @sText = Ltrim(right(@sText,Datalength(@sText)-@iStrike))
			Set @sText = right(@sText,Datalength(@sText)-@iStrike)
		end
		else
		begin
			Set @value = @sText
			if '>'+@value+'<' <> '><' Insert @retArray values(@idx,@value)
			Set @bcontinue = 0
		end
	end
end
else
begin
	while @bcontinue =1
	begin	
		If Datalength(@sText)>1
		begin
			Set @value = Substring(@sText,1,1)
			Insert @retArray values(@idx,@value)
			Set @idx = @idx + 1
			Set @sText = Substring(@sText,2,Datalength(@sText)-1)
		end
		else
		begin
			Insert @retArray values(@idx,@value)
			Set @bcontinue = 0
		end
	end
end
RETURN
END

Rate

5 (1)

Share

Share

Rate

5 (1)