SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fnSplit] (
@sInputList VARCHAR(8000) -- List of delimited items
, @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
)
RETURNS @List TABLE (Offset int, item VARCHAR(8000))
BEGIN
DECLARE @sItem VARCHAR(8000)
declare @index int
set @index = 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))))
IF LEN(@sItem) > 0
INSERT INTO @List SELECT @index, @sItem
set @index = @index + 1
END
IF LEN(@sInputList) > 0
INSERT INTO @List SELECT @index, @sInputList
set @index = @index + 1 -- Put the last item in
RETURN
END
This Function returns a Table with two fields.
1) Offset a numeric value you can also use that a Sequence no and
2) Item
Thank you.