34,838
社区成员




/*************************************************************************************
* 根据带分割符的值,分拆成多个值,
输入 '1|2|3', '1' 返回1 2 3
**************************************************************************************/
CREATE Procedure up_Sys_getPart
@sStr varchar(4000) ='',
@sSplit varchar(1) =''
As
create table #A_getPart (Fld varchar(4000))
insert #A_getPart (Fld)
values (@sStr)
--------------开始查询--------------------------
select SUBSTRING(a.Fld, number, CHARINDEX(@sSplit, a.Fld +@sSplit, number) -number) as Fld
from #A_getPart a, master..spt_values
where number >=1 and number <=len(a.Fld)
and type ='p'
and substring(@sSplit +a.Fld, number, 1) =@sSplit
drop table #A_getPart
GO
declare @para nvarchar(max) = '20140100028|20140100027'
declare @x xml = '<id>' + REPLACE(@para,'|','</id><id>') + '</id>'
select T.d.value('.','varchar(100)') as value from @x.nodes('/*') as T(D)