sqlserver中存储过程拆分字符串问题

[完美灬缺陷]

传入一个参数假如值为1，2，3， 如何在存储过程中用，拆分得到单个值。
有的直接贴实例、谢谢!

[--小F--]

　　use tempdb
　　go
　　--测试数据
　　declare @s varchar(1000)
　　set @s='ak47,mp5,1,23'
　　/*要求输出结果
　　S
　　----
　　ak47
　　mp5
　　1
　　23
　　*/
　　--3种方法对比:
　　--1.[朴实]动态Exec方法:
　　declare @s1 varchar(1000)
　　set @s1=right(replace(','+@s,',',''' as S union select '''),len(replace(','+@s,',',''' as S union select '''))-12)+''''
　　exec(@s1)
　　--2.[变通]表交叉方法:
　　select replace(reverse((left(s,charindex(',',s)))),',','') as S from(
　　select r,reverse(left(@s,r))+',' as s
　　from(
　　select (select count(*) from sysobjects where name<=t.name ) as r
　　from sysobjects t
　　)a where r<=len(@s)
　　and left(@s+',',r+1) like '%,'
　　)t order by r
　　--3.[高级]XML方法:
　　DECLARE @idoc int;
　　DECLARE @doc xml;
　　set @doc=cast('<Root><item><S>'+replace(@s,',','</S></item><item><S>')+'</S></item></Root>' as xml)
　　EXEC sp_xml_preparedocument @Idoc OUTPUT, @doc
　　SELECT * FROM OPENXML (@Idoc, '/Root/item',2)
　　WITH (
　　[S] varchar(10)
　　) 

[--小F--]

--参考
拆分表:

--> --> (Roy)生成測試數據
 
if not object_id('Tab') is null
    drop table Tab
Go
Create table Tab([Col1] int,[COl2] nvarchar(5))
Insert Tab
select 1,N'a,b,c' union all
select 2,N'd,e' union all
select 3,N'f'
Go

--SQL2000用辅助表:
if object_id('Tempdb..#Num') is not null
    drop table #Num
go
select top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns b
Select 
    a.Col1,COl2=substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID) 
from 
    Tab a,#Num b
where
    charindex(',',','+a.Col2,b.ID)=b.ID --也可用 substring(','+a.COl2,b.ID,1)=','
--2000不使用辅助表
Select
    a.Col1,COl2=substring(a.Col2,b.number,charindex(',',a.Col2+',',b.number)-b.number) 
from 
    Tab a join master..spt_values  b 
    ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.col2)
where
     substring(','+a.COl2,b.number,1)=','


SQL2005用Xml:

select 
    a.COl1,b.Col2
from 
    (select Col1,COl2=convert(xml,'<root><v>'+replace(COl2,',','</v><v>')+'</v></root>') from Tab)a
outer apply
    (select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v')C(v))b




SQL05用CTE:

;with roy as 
(select Col1,COl2=cast(left(Col2,charindex(',',Col2+',')-1) as nvarchar(100)),Split=cast(stuff(COl2+',',1,charindex(',',Col2+','),'') as nvarchar(100)) from Tab
union all
select Col1,COl2=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from Roy where split>''
)
select COl1,COl2 from roy order by COl1 option (MAXRECURSION 0)

生成结果:
/*
Col1        COl2
----------- -----
1           a
1           b
1           c
2           d
2           e
3           f
*/

[xuam]

select  substring(a.列名 , b.number , charindex(',' , a.列名 + ',' , b.number) - b.number) 
from tb a join master..spt_values  b 
    on b.type='p' and b.number between 1 and len(a.列名)
where substring(',' + a.列名 , b.number , 1) = ','

[叶子]

可以用自定义函数：
参考：
http://wangwdding.blog.163.com/blog/static/13419138620112226428866/

[jmx123456789]

create proc test 
@ids varchar(5000)
as
begin
    declare @id int

    set @ids=@ids+','
    while(@ids<>'')
		begin
			set @id=convert(int,left(@ids,Charindex(',',@ids,1)-1))
			print @id--为了演示就直接打印了
			set @ids=stuff(@ids,1,charindex(',',@ids,1),'')
		end
end

exec test @ids='1,2,3,51'

--------------------------
1
2
3
51

[AcHerat]

declare @str varchar(100)
set @str = '1,2,3'

select @str = 'select ' + replace(@str,',',' as id union all select ')
exec(@str)

/************

id
-----------
1
2
3

(3 行受影响)

[rucypli]

declare @str varchar(100)
set @str = '1,2,3'

select @str = 'select ' + replace(@str,',',' as id union all select ')
exec(@str)

/************

id
-----------
1
2
3

(3 行受影响)

[超凡]

DECLARE @str varchar(10)
SET @str='123'

select substring(@str,number,1)
from master..spt_values
where type='P' and number<=len(@str)

[超凡]

DECLARE @str varchar(5)
SET @str='1,2,3'

select substring(replace(@str,',',''),number,1)
from master..spt_values
where type='P' and number<=len(@str)