SQL Script to list down the strings with delimiter

I have see lot of queries asking for down the strings from comma seperated string or semicolon seperated string. Below is a superb function to acheive the same.

CREATE FUNCTION dbo.fnSplit(
@INPUTSTRING VARCHAR(8000) -- Actual string with comma delimeted

, @Delimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))
BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@Delimiter,@INPUTSTRING,0) <> 0
BEGIN
SELECT
@sItem=RTRIM(LTRIM(SUBSTRING(@INPUTSTRING,1,CHARINDEX(@Delimiter,@INPUTSTRING,0)-1))),
@INPUTSTRING=RTRIM(LTRIM(SUBSTRING(@INPUTSTRING,CHARINDEX(@Delimiter,@INPUTSTRING,0)+LEN(@Delimiter),LEN(@INPUTSTRING))))
IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem
END
IF LEN(@INPUTSTRING) > 0
INSERT INTO @List SELECT @INPUTSTRING -- Put the last item in
RETURN
END
GO

The above code with create a function which will split the string based on its input parameter.

Lets execute the below query,

select * from dbo.fnSplit('1,22,333,444,,5555,666', ',')

The above query will fetch the output,

item

-------------------------

1

22

333

444

5555

666

Happy Learning!!!

Regards,

Venkatesan Prabu .J