字符串分解3

CREATE FUNCTION [dbo].[ufn_Tab]
(
@chvnKeyWords NVARCHAR(4000), -- 要分隔的关键字字符串集
@chvnSeparator NCHAR(1), -- 要使用的分隔符，默认为'|'
@chvn NCHAR(10),
@chvnS NCHAR(50)
) RETURNS @tResult TABLE (
Num INT IDENTITY(1, 1) NOT NULL,
Word NVARCHAR(200) NOT NULL,
chvn NVARCHAR(10) NOT NULL,
chvnS NVARCHAR(50) NOT NULL
)
--$Encode$--
--==================================
-- 功能：分隔关键字字串集插入数据表
-- 作者: XXXX
-- 创建: XXXX-XX-XX
-- 修改: XXXX-XX-XX XX XXXXX
-- 调用:SELECT * FROM dbo.ufn_SplitToTable2('1|2|3','|')
-- SELECT * FROM dbo.ufn_STable('AAA-BB-00060 70A~85B/AAA-BB-00060A 80D~85D','/','','')
-- SELECT * FROM dbo.ufn_Table('style A/C/E:Black/Skin/Lilac',' ','','')
-- SELECT * FROM dbo.ufn_Tab('AAA-BB-00040/39/38','/','','')
--==================================
AS
BEGIN
DECLARE @Column15 VARCHAR(100)
DECLARE @Column16 VARCHAR(100)
DECLARE @Column17 VARCHAR(100)
DECLARE @Column18 VARCHAR(100)
DECLARE order_c CURSOR FOR ------------一层游标------------------------------------
SELECT ltrim(rtrim(WORD)) FROM dbo.ufn_SplitToTable(ltrim(rtrim(@chvnKeyWords)),ltrim(rtrim(@chvnSeparator)),ltrim(rtrim(@chvn)),ltrim(rtrim(@chvnS)))
--打开游标--
OPEN order_c
--开始循环游标变量--
FETCH next FROM order_c INTO @Column15
WHILE @@FETCH_STATUS = 0 --返回被 FETCH语句执行的最后游标的状态--
BEGIN
if len(@Column15) > 5
begin
select @Column16 = left(@Column15,7)
select @Column18 = @Column15
end
else if len(@Column15) < 5
begin
select @Column17 = ''
select @Column17 = right('00000' + convert(varchar,@Column15),5)
select @Column18 = @Column16 + @Column17
end
insert @tResult(Word,chvn,chvnS)
VALUES(@Column18,@chvn,@chvnS);
FETCH next FROM order_c INTO @Column15
END
CLOSE order_c --------------------关闭一层游标-------------------------
DEALLOCATE order_c --------------------释放一层游标-------------------------
RETURN
END

GO