1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43
| ALTER FUNCTION [dbo].[fnParseList]
(
@Text NVARCHAR(MAX),
@Delimiter CHAR
)
RETURNS @Result TABLE (RowID SMALLINT IDENTITY(1, 1) PRIMARY KEY, Data NVARCHAR(4000))
AS
BEGIN
DECLARE @NextPos INT,
@LastPos INT
SELECT @NextPos = CHARINDEX(@Delimiter, @Text, 1),
@LastPos = 0
WHILE @NextPos > 0
BEGIN
INSERT @Result
(
Data
)
SELECT SUBSTRING(@Text, @LastPos + 1, @NextPos - @LastPos - 1)
SELECT @LastPos = @NextPos,
@NextPos = CHARINDEX(@Delimiter, @Text, @NextPos + 1)
END
IF SCOPE_IDENTITY() > 0
INSERT @Result
(
Data
)
SELECT SUBSTRING(@Text, @LastPos + 1, DATALENGTH(@Text) - @LastPos)
ELSE
INSERT @Result
(
Data
)
SELECT @Text
RETURN
END |
Partager