CREATE Procedure up_Sys_getPart
@sStr varchar(max) ='',--如果没有max就用8000
@sSplit varchar(1) =''
As
create table #A_getPart (Fld varchar(max))--如果没有max就用8000
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
@sStr varchar(max) ='',--如果没有max就用8000
@sSplit varchar(1) =''
As
create table #A_getPart (Fld varchar(max))--如果没有max就用8000
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
是不是里面的处理函数参数有255个字符限制
2)不使用master..spt_values 自己构建辅助表 比如10W条序号表,根据实际需要
master..spt_values 中type='P'只有2048条
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)
只好用text对付它