* SQLServer字符串批量拆分函数

发布时间：2020-12-12 14:03:24 所属栏目：MsSql教程来源：网络整理

导读：/****************************************************** ?*?SQLServer字符串批量拆分函数 --原始数据? id??????????names? -----------?--------------------? 1???????????ljy,zwg,cym? 2???????????czg,zy --生成的数据? id??????????rn??????????name?

/******************************************************
?*?SQLServer字符串批量拆分函数
--原始数据?
id??????????names?
-----------?--------------------?
1???????????ljy,zwg,cym?
2???????????czg,zy
--生成的数据?
id??????????rn??????????name?
-----------?-----------?----------?
1???????????1???????????ljy?
1???????????2???????????zwg?
1???????????3???????????cym?
2???????????1???????????czg?
2???????????2???????????zy

--示例1:
declare?@table?table(id?int?,?names?varchar(20))
insert?into?@table?select?1,?'ljy,cym'?
insert?into?@table?select?2?,?'czg,zy'
SELECT?*?FROM?@table?
SELECT?a.id,rn,b.v?AS?name?
FROM?@table?AS?a?outer?apply?dbo.uf_split_string_batch(a.names)b
--示例2:
SELECT?a.c_guid,b.v?AS?name?
FROM?tb_contract?a?
outer?apply?dbo.uf_split_string_batch(a.c_store_id)b
*************************************************************************/

ALTER?FUNCTION?[dbo].[uf_split_string_batch](@value?varchar(MAX))
RETURNS?@table?TABLE(rn?int,v?varchar(MAX))
AS
BEGIN
????INSERT?INTO?@table
????SELECT?b.*?
????FROM(SELECT?CONVERT(XML?,?'<v>'?+?REPLACE(@value,?',','<

><v>'?)+?'<

>'?)?AS?f?)a
?????OUTER?APPLY
????(
?????????SELECT?rn?=?row_number?()OVER?(ORDER?BY?getdate?()),t.c.value?('.'?,?'varchar(max)'?)?AS?f
?????????FROM?a.f.nodes?('/

'?)?AS?t(c?) ?????)b ????RETURN END

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!