22,210
社区成员
发帖
与我相关
我的任务
分享
CREATE FUNCTION dbo.f_splitSTR
(
@s VARCHAR(8000) , --待分拆的字符串
@split VARCHAR(10) --数据分隔符
)
RETURNS @re TABLE ( col VARCHAR(100) )
AS
BEGIN
DECLARE @splitlen INT;
SET @splitlen = LEN(@split + 'a') - 2;
WHILE CHARINDEX(@split, @s) > 0
BEGIN
INSERT @re
VALUES ( LEFT(@s, CHARINDEX(@split, @s) - 1) );
SET @s = STUFF(@s, 1, CHARINDEX(@split, @s) + @splitlen, '');
END;
INSERT @re
VALUES ( @s );
RETURN;
END;
GO
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] nvarchar(22),[Name] nvarchar(44))
Insert #T
select N'01',N'[人工]:30,[物料]:100,[单价]:60' union all
select N'02',N'[物料]:90' union all
select N'03',N'[人工]:100,[物料]:120' union all
select N'04',N'[单价]:100,[物料]:200'
Go
--测试数据结束
SELECT DISTINCT
SUBSTRING(t.col, CHARINDEX('[', t.col) + 1,
CHARINDEX(']', t.col) - CHARINDEX('[', t.col) - 1) AS name
FROM #T
CROSS APPLY ( SELECT col
FROM dbo.f_splitSTR(Name, ',')
) t;