22,210
社区成员
发帖
与我相关
我的任务
分享
--新建函数:
CREATE FUNCTION dbo.f_splite
(
@s VARCHAR(8000) , --待分拆的字符串
@split VARCHAR(10) --数据分隔符
)
RETURNS @re TABLE (id INT,col VARCHAR(100) )
AS
BEGIN
DECLARE @splitlen INT
DECLARE @i INT = 1
SET @splitlen = LEN(@split + 'a') - 2
WHILE CHARINDEX(@split, @s) > 0
BEGIN
INSERT @re
VALUES ( @i,LEFT(@s, CHARINDEX(@split, @s) - 1) )
SET @s = STUFF(@s, 1, CHARINDEX(@split, @s) + @splitlen, '')
SET @i = @i +1
END
INSERT @re
VALUES ( @i,@s )
RETURN
END
GO
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([订单号] nvarchar(25),[货品明细] nvarchar(28))
Insert #T
select N'A1001',N'苹果,香蕉' union all
select N'A1002',N'葡萄' union all
select N'A1003',N'雪梨,西瓜,葡萄'
Go
--测试数据结束
SELECT 订单号,
id AS 序号,
t.col AS 货品
FROM #T
CROSS APPLY
(SELECT * FROM dbo.f_splite([货品明细], ',') ) t;