Split Function in SQL Server

Posted by Techie Cocktail | 1:43 PM | | 0 comments »

Did you ever want to split a comma/space/<any character> delimited long string?
In .Net we have the in-built split function available to perform this task.

Check this below function in SQL Server:


CREATE FUNCTION dbo.splitString(
@sInputList VARCHAR(8000) -- List of delimited items
, @sDelimiter VARCHAR(8000) = ' ' -- delimiter that separates items. Space in this case.
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
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))))

IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem
END

IF LEN(@sInputList) > 0
INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
GO

-- This is how you would test
select * from splitString('This is a split function', ' ')

-- Output
This
is
a
split
function


References: http://searchwindevelopment.techtarget.com/tip/0,289483,sid8_gci932171,00.html

0 comments