IF OBJECT_ID('fn_split', 'FN') IS NOT NULL
DROP FUNCTION fn_split
GO
CREATE FUNCTION [dbo].[fn_split]
(
@Source VARCHAR(MAX),
@Separator CHAR(1)
)
RETURNS @TB TABLE(VL VARCHAR(100))
AS
BEGIN
DECLARE @TMP VARCHAR(MAX)
DECLARE @VL VARCHAR(100)
SET @TMP = @Source
WHILE @TMP <> ''
BEGIN
IF CHARINDEX(@Separator, @TMP) = 0
BEGIN
SET @VL = LTRIM(RTRIM(@TMP))
IF @VL <> ''
BEGIN
INSERT INTO @TB
SELECT @VL
SET @TMP = ''
END
END
ELSE
BEGIN
SET @VL = SUBSTRING(@TMP, 0, CHARINDEX(@Separator, @TMP))
SET @VL = LTRIM(RTRIM(@VL))
IF @VL <> ''
BEGIN
INSERT INTO @TB
SELECT @VL
END
SET @TMP = SUBSTRING(@TMP, CHARINDEX(@Separator, @TMP) + 1, LEN(@TMP))
SET @TMP = LTRIM(RTRIM(@TMP))
END
END
RETURN;
END