CREATE FUNCTION usf_SplitString (@delimitedString nvarchar(max), @mDelimiter char(1) = ‘y’)

Returns @tblStringToTable Table (Colvalue nvarchar(50))AS

BEGIN

Declare @colvalue nvarchar(50)
Declare @startpos int, @length int
while LEN(@delimitedString) > 0

BEGIN
SET @StartPos = CHARINDEX(@mDelimiter, @DelimitedString)
IF @StartPos < 0 set @StartPos = 0
SET @Length = LEN(@DelimitedString) – @StartPos – 1
IF @Length < 0 SET @Length = 0
IF @StartPos > 0
BEGIN
SET @colvalue = SUBSTRING(@delimitedString,1,@StartPos – 1)
Set @delimitedString = SUBSTRING(@delimitedString. @StartPos+ 1, LEN(@delimitedString ) – @StartPos)
END

Else
BEGIN

Set @colvalue = @delimitedString;
Set @delimitedString = ”

END
IF @Colvalue != ”
IF not exists(Select 1 from @delimitedString where Colvalue = @Colvalue)
INSERT @tblStringToTable (Colvalue)  VALUES (@Colvalue)
END
RETURN

END

GO

See Below for example usage – don’t forget you must specify database owner

select * from dbo.usf_SplitString (‘1,2,3,4′,’,’)

Returns a table

Colvalue 1 2 3 4 ‘