SqlServer中解析字符串的表值函数

?在数据库中，经常用到了将一定格式的字符串解析的问题。以前写过类似的方法，今天又重新写了一个，发上来共享给大家。

1. Create?function?[dbo].[Func_SplitStringToTable]???
   (??
   ????--?需要解析的字符串??
   ????@str?nvarchar(max),??
   ????--?分隔符??
   ????@split?varchar(10)??
   )??
   returns?@t?Table?(c1?varchar(100))??
   as??
   ????begin??
   ????????declare?@i?int??
   ????????declare?@s?int??????
   ????????set?@i=1??????
   ????????set?@s=1??????
   ????????while(@i>0)??
   ????????????begin??????????????
   ????????????????set?@i=charindex(@split,@str,@s)??????????
   ????????????????if(@i>0)??????????
   ????????????????????begin??????????????
   ????????????????????????insert?@t(c1)?values(substring(@str,@s,@i-@s))??
   ????????????????????end?????????????
   ????????????????else???
   ????????????????????begin??????????????
   ????????????????????????insert?@t(c1)?values(substring(@str,len(@str)-@s+1))??????????
   ????????????????????end??????????
   ????????????????set?@s?=?@i?+?1?????????
   ????????????end??????
   ????????return??
   ????end

Create?function?[dbo].[Func_SplitStringToTable]?
(
	--?需要解析的字符串
	@str?nvarchar(max),--?分隔符
	@split?varchar(10)
)
returns?@t?Table?(c1?varchar(100))
as
	begin
	????declare?@i?int
	????declare?@s?int????
	????set?@i=1????
	????set?@s=1????
	????while(@i>0)
	????????begin????????????
				set?@i=charindex(@split,@s)????????
				if(@i>0)????????
					begin????????????
						insert?@t(c1)?values(substring(@str,@i-@s))
	????????????????end???????????
	????????????else?
	????????????????begin????????????
						insert?@t(c1)?values(substring(@str,len(@str)-@s+1))????????
	????????????????end????????
				set?@s?=?@i?+?1???????
			end????
	????return
????end

使用的时候如下：

select?*?from?dbo.Func_SplitStringToTable('1,15,13',',');

（编辑：李大同）

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