CREATE FUNCTION [udf].[SplitDelimitedString](
@String NVARCHAR (MAX)
,@Delimiter NVARCHAR (10))
RETURNS @ValueTable TABLE (
[Value] NVARCHAR(4000))
BEGIN
DECLARE @NextString NVARCHAR(4000)
DECLARE @Pos INT
DECLARE @NextPos INT
DECLARE @CommaCheck NVARCHAR(1)
--Initialize
SET @NextString = ''
SET @CommaCheck = right(@String,1)
SET @Delimiter = ISNULL(@Delimiter,N',')
--Check for trailing Comma, if not exists, INSERT
IF (@CommaCheck <> @Delimiter)
SET @String = @String + @Delimiter
--Get position of first Comma
SET @Pos = charindex(@Delimiter,@String)
SET @NextPos = 1
--Loop while there is still a comma in the String of levels
WHILE (@Pos <> 0)
BEGIN
SET @NextString = substring(@String,1,@Pos - 1)
INSERT @ValueTable
( [Value])
VALUES
(@NextString)
SET @String = substring(@String,@Pos +1,len(@String))
SET @NextPos = @Pos
SET @Pos = charindex(@Delimiter,@String)
END
RETURN
END
GO
@String NVARCHAR (MAX)
,@Delimiter NVARCHAR (10))
RETURNS @ValueTable TABLE (
[Value] NVARCHAR(4000))
BEGIN
DECLARE @NextString NVARCHAR(4000)
DECLARE @Pos INT
DECLARE @NextPos INT
DECLARE @CommaCheck NVARCHAR(1)
--Initialize
SET @NextString = ''
SET @CommaCheck = right(@String,1)
SET @Delimiter = ISNULL(@Delimiter,N',')
--Check for trailing Comma, if not exists, INSERT
IF (@CommaCheck <> @Delimiter)
SET @String = @String + @Delimiter
--Get position of first Comma
SET @Pos = charindex(@Delimiter,@String)
SET @NextPos = 1
--Loop while there is still a comma in the String of levels
WHILE (@Pos <> 0)
BEGIN
SET @NextString = substring(@String,1,@Pos - 1)
INSERT @ValueTable
( [Value])
VALUES
(@NextString)
SET @String = substring(@String,@Pos +1,len(@String))
SET @NextPos = @Pos
SET @Pos = charindex(@Delimiter,@String)
END
RETURN
END
GO