CREATE FUNCTION SYS_SPLIT(@srcStr NVARCHAR(MAX),@splitStr NVARCHAR(100))
RETURNS @tmp TABLE(
id INT IDENTITY PRIMARY KEY,
content NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @str NVARCHAR(MAX);
DECLARE @len INT;
DECLARE @num INT;
DECLARE @idx INT;
DECLARE @start INT;
SET @num = LEN(@splitStr);
SET @len = LEN(@srcStr);
SET @idx = 0;
SET @start = 0;
WHILE @idx <= @len
BEGIN
IF LEFT(RIGHT(@srcStr, @len - @idx), @num) = @splitStr
BEGIN
SET @str = LEFT(RIGHT(@srcStr, @len - @start), @idx - @start);
IF LEN(@str) > 0
BEGIN
INSERT INTO @tmp SELECT @str;
END
SET @idx = @idx + @num;
SET @start = @idx;
END
ELSE
BEGIN
SET @idx = @idx + 1;
END
END
IF @start != @idx
BEGIN
SET @str = LEFT(RIGHT(@srcStr, @len - @start), @idx - @start - 1);
IF LEN(@str) > 0
BEGIN
INSERT INTO @tmp SELECT @str;
END
END
RETURN
END
另一种写法:
CREATE FUNCTION MY_SPLIT(@srcStr NVARCHAR(MAX),@splitStr NVARCHAR(100))
RETURNS @tmp TABLE(
id INT IDENTITY PRIMARY KEY,
content NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @str NVARCHAR(MAX);
DECLARE @len INT;
DECLARE @idx INT;
SET @len = LEN(@splitStr);
WHILE LEN(@srcStr) > 0
BEGIN
SET @idx = CHARINDEX(@splitStr, @srcStr);
IF @idx = 0
BEGIN
SET @str = @srcStr;
SET @srcStr = '';
END
ELSE
BEGIN
SET @str = LEFT(@srcStr, @idx - 1);
SET @srcStr = RIGHT(@srcStr, LEN(@srcStr) - LEN(@str) - @len);
END
IF LEN(@str) > 0
INSERT INTO @tmp SELECT @str;
END
RETURN
END
需要注意的是,上面的方法是一个通用的解决思路,稍稍修改,就可以用到各sql数据库,
而微软从sql server2016开始,内置了STRING_SPLIT(sourceStr, splitStr)函数,大家可以直接用,不用自己写split用户函数了