在sqlserver中，怎么把 带分割符的值分拆成多个值

[披着虎皮 的石头]

在sqlserver中，怎么把下面的值分拆成多个值
1,2,3,4,abc,dresas

分别是
1
2
3
4
abc
dresas

[發糞塗牆]

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2014-01-06 14:54:40
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
--	Dec 28 2012 20:23:12 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([name] varchar(18))
insert [huang]
select '1,2,3,4,abc,dresas'
--------------开始查询--------------------------
select
    SUBSTRING(a.[name],number,CHARINDEX(',',a.[name]+',',number)-number) as [name] 
from
    [huang] a,master..spt_values 
where
    number >=1 and number<=len(a.[name])  
    and type='p' 
    and substring(','+a.[name],number,1)=','
----------------结果----------------------------
/* 
name
------------------
1
2
3
4
abc
dresas

*/

[發糞塗牆]

也可以用函数

if exists(select * from sys.objects where name = 'f_splitSTR' and type = 'tf')
   drop function dbo.f_splitSTR
go
 
create function dbo.f_splitSTR
(
    @s varchar(8000),     --要分拆的字符串
    @split varchar(10)    --分隔字符
) 
returns @re table(                      --要返回的临时表
                     col varchar(10)  --临时表中的列 
                 )
as
begin   
  declare @len int
   
  set @len = LEN(@split)      --分隔符不一定就是一个字符，可能是2个字符
   
  while CHARINDEX(@split,@s) >0
  begin
    insert into @re 
    values(left(@s,charindex(@split,@s) - 1))
     
    set @s = STUFF(@s,1,charindex(@split,@s) - 1 + @len ,'')    --覆盖：字符串以及分隔符
  end
   
  insert into @re values(@s)
   
  return   --返回临时表
end
go  
 
 
 
declare @res varchar(100)
set @res='1,2,3,4,abc,dresas';
 
set @res = REPLACE(@res,' ',',')
 
select * from dbo.f_splitSTR(@res,',') t 
/*
col
----------
1
2
3
4
abc
dresas
*/

[山寨DBA]

我的方法有点繁琐：

create table #a (
	X varchar(100) null
 )
create table #last (
	X varchar(100) null
 )
 
create table #linshi (
	X varchar(100) null
 )
insert into #a
select '1,2,3,4,abc,dresas'

declare @i int,@j int,@x int
set @i=1
select @j=LEN(X) from #a
while(@i<@j)
begin
	
	select @x=CHARINDEX(',',X) from #a 
	if @x<>''
	begin
		insert into #last
		select SUBSTRING(X,1,CHARINDEX(',',X)-1) from #a 
		insert into #linshi
		select SUBSTRING(X,CHARINDEX(',',X)+1,LEN(X)) from #a 
		truncate table #a
		insert into #a
		select * from #linshi
		truncate table #linshi
		set @i=@i+1
	end
	else
	begin
		insert into #last
		select * from #a
		set @i= @j
	end
	
end

select * From #last

[山寨DBA]

结果： 1 2 3 4 abc dresas