SQL分隔字元串

創建函數：

CREATE FUNCTION[dbo].[split](@str NVARCHAR(max),@spliter NVARCHAR(10))
--@str:目標字元串
--@spliter:分隔符
RETURNS @tb TABLE(ch NVARCHAR(max))
AS
BEGIN
DECLARE @Num INT,@Pos INT, @NextPos INT
SET @Num = 0
SET @Pos = 1
WHILE(@Pos <= LEN(@str))
BEGIN
SELECT @NextPos = CHARINDEX(@spliter, @str, @Pos)
IF (@NextPos = 0 OR @NextPos IS NULL)
SELECT @NextPos = LEN(@str) + 1
INSERT INTO @tb VALUES(RTRIM(LTRIM(SUBSTRING(@str, @Pos, @NextPos - @Pos))))
SELECT @Pos = @NextPos+1
END
RETURN
END
GO

使用方式：

SELECT * FROM dbo.split('1,2,3,4',',') --註：多行單列的數據表

測試語句：

IF object_id(N'[dbo].[split]') IS NOT NULL --檢查函數【[dbo].[split]】是否存在
BEGIN
    DROP FUNCTION [dbo].[split] --刪除函數
END
GO


CREATE FUNCTION[dbo].[split](@str NVARCHAR(max),@spliter NVARCHAR(10))
--@str:目標字元串
--@spliter:分隔符
RETURNS @tb TABLE(ch NVARCHAR(max))
AS
BEGIN
DECLARE @Num INT,@Pos INT, @NextPos INT
SET @Num = 0
SET @Pos = 1
WHILE(@Pos <= LEN(@str))
BEGIN
SELECT @NextPos = CHARINDEX(@spliter, @str, @Pos)
IF (@NextPos = 0 OR @NextPos IS NULL)
SELECT @NextPos = LEN(@str) + 1
INSERT INTO @tb VALUES(RTRIM(LTRIM(SUBSTRING(@str, @Pos, @NextPos - @Pos))))
SELECT @Pos = @NextPos+1
END
RETURN
END
GO

SELECT * FROM dbo.split('1,2,3,4',',')

DROP FUNCTION [dbo].[split]
GO

說明：

　　1、僅支持文本字元串分隔！！！