日期:2014-05-18 浏览次数:20761 次
if object_id('f_split')is not null drop function f_split go create function f_split ( @s varchar(8000), --待分拆的字符串 @split varchar(10) --数据分隔符 )returns table as return ( select substring(@s,number,charindex(@split,@s+@split,number)-number)as col from master..spt_values where type='p' and number<=len(@s+'a') and charindex(@split,@split+@s,number)=number ) go select * from dbo.f_split('11,2,3',',') /* col ---- 11 2 3 (3 個資料列受到影響) */
------解决方案--------------------
declare @M varchar(50), @N xml set @M='10663,10662,10661,10660' set @N=convert(xml,'<r><c>'+replace(@M,',','</c><c>')+'</c></r>') select col=T.c.value('.[1]','varchar(20)') from @N.nodes('/r/c') T(c) /* col -------------------- 10663 10662 10661 10660 */