字符串分解1

--==================================
-- 功能：分隔关键字字串集插入数据表
-- 作者: XXXX
-- 创建: XXXX-XX-XX
-- 修改: XXXX-XX-XX XX XXXXX
-- 调用:SELECT * FROM dbo.ufn_SplitToTable2('1|2|3','|')
--==================================
CREATE FUNCTION [dbo].[ufn_SplitToTable]
(
@chvnKeyWords NVARCHAR(4000), -- 要分隔的关键字字符串集
@chvnSeparator NCHAR(1), -- 要使用的分隔符，默认为'|'
@chvn NCHAR(10),
@chvnS NCHAR(50)
) RETURNS @tblResult TABLE (
Num INT IDENTITY(1, 1) NOT NULL,
Word NVARCHAR(200) NOT NULL,
chvn NVARCHAR(10) NOT NULL,
chvnS NVARCHAR(50) NOT NULL
)
--$Encode$--
AS
BEGIN
-- 分隔符参数为NULL时则使用"|"
SET @chvnSeparator = ISNULL(@chvnSeparator, N'|');

DECLARE @intPos AS INT = 0;
SET @intPos = CHARINDEX(@chvnSeparator, @chvnKeyWords);

WHILE @intPos >= 1 /* @intPos > 0*/
BEGIN
IF @intPos >= 2 /*@intPos > 1*/
BEGIN
INSERT INTO @tblResult (Word,chvn,chvnS)
VALUES (LEFT(@chvnKeyWords, @intPos - 1),@chvn,@chvnS);
END

-- 也可以使用SUBSTRING字符串函数
--SET @chvnKeyWords = SUBSTRING(@chvnKeyWords, @intPos + 1, LEN(@chvnKeyWords) - (@intPos + 1) + 1);

SET @chvnKeyWords = STUFF(@chvnKeyWords, 1, @intPos, N'');
SET @intPos = CHARINDEX(@chvnSeparator, @chvnKeyWords);
END

IF @chvnKeyWords > N''
BEGIN
INSERT INTO @tblResult (Word,chvn,chvnS)
VALUES(@chvnKeyWords,@chvn,@chvnS);
END

RETURN;
END

GO