SQL拆分字段 【转】

拆分字段与合并字段是字符串处理常见的两个问题，下面将针对实例进行分析求解。
问题：將字符串拆分成記錄集
原表Table1(id int identity(1,1),name varchar(100))
id name
-------------------
1 'a,b,c'
2 'e,f'

希望得到结果：
id name
-----------
1 'a'
1 'b'
1 'c'
2 'e'
2 'f'

一、首先我们利用最原始的方法进行求解，即通过逐条循环来实现。
create table #a(id int identity(1,1),name varchar(100))
insert #a(name)
select 'a,b,c'
union all select 'd,e,f'
union all select 'g,h'
1.利用游标
DECLARE @id int,@name varchar(100)
create table #b(id int,name varchar(100))
DECLARE a_cursor CURSOR FOR
SELECT id, name
FROM #a
ORDER BY id

OPEN a_cursor

FETCH NEXT FROM a_cursor
INTO @id, @name

WHILE @@FETCH_STATUS = 0
BEGIN
while charindex(',',@name)>0
begin
insert #b(id,name)
select @id,left(@name,charindex(',',@name)-1)
set @name=right(@name,len(@name)-charindex(',',@name))
end
insert #b(id,name)
select @id,@name
FETCH NEXT FROM a_cursor
INTO @id, @name
end
CLOSE a_cursor
DEALLOCATE a_cursor

select *from #b
drop table #b
2.利用临时表代替游标
DECLARE @id int,@name varchar(100),@count int
create table #tmp(no int identity(1,1),id int,name varchar(100))
create table #b(id int,name varchar(100))
insert #tmp(id,name)
select * from #a
set @count=@@rowcount
while @count>0
begin
select @id=id,@name=name from #tmp where no=@count
while charindex(',',@name)>0
begin
insert #b(id,name)
select @id,left(@name,charindex(',',@name)-1)
set @name=right(@name,len(@name)-charindex(',',@name))
end
insert #b(id,name)
select @id,@name
set @count=@count-1
end

select * from #b order by id,name
drop table #b,#tmp
上述两种方法都两次利用了循环，清晰易懂，但代码长而且速度慢。

二、利用replace函数与动态语句结合。
-- test:
create table #a(id int identity(1,1),name varchar(100))
create table #b(id int,name varchar(100))
insert #a(name)
select 'a,b,c'
union all select 'd,e,f'
union all select 'g,h'
go
declare @sql varchar(8000)
set @sql = ''
select @sql = @sql + ' insert into #b select ' + cast(id as varchar) + ',''' + replace(name,',',''' union all select ' + cast(id as varchar) + ',''') + '''' from #a
--print @sql
exec (@sql)
go
select * from #b
go
drop table #a,#b

解析：此段代码很简洁，但使用了比较复杂的动态语句与代替函数的使用技巧，给程序的解读带来一定的困难，而且受字段串长度的限制，
一旦超过8000个字符就不能满足要求。

三、利用substring函数计算出每一段分割符的上下位置，从而达到分离字段的目的。
此方法可以处理较大数据量的要求而且速度较快，但此方法比较巧妙，难以联想到。
create table #a(id int identity(1,1),name varchar(100))

insert #a(name)
select 'a,b,c'
union all select 'd,e,f'
union all select 'g,h'

select a.id,name=substring(a.name,b.id,charindex(',',a.name+',',b.id)-b.id)
from #a a join(
select top 100 id=a.id+b.id+1
from(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) a,(
select id=0 union all select 10
union all select id=20 union all select 30
union all select id=40 union all select 50
union all select id=60 union all select 70
union all select id=80 union all select 90
) b
)b on substring(','+a.name,b.id,1)=','

drop table #a
综合起来，上述三种方法相比：
第一种通俗易懂，符合一般人的编写思想，但效率不高。
第二种方法语句短小精悍，较率也较高，但由于受字符串长度限制，通用性不好，难以处理大批量的数据。
第三种方法弥补了第二种方法的不足，但同时加进了程序的复杂度，不是很容易想得到，想得通。

四、 Ntext字段的拆分

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_GetStr]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_GetStr]
GO

/*--得到字符串列表指定位置的字符

可以自定义字符串列表的分隔符
如果取数位置超出的范围,返回空字符串

--邹建 2005.04(引用请保留此信息)--*/

/*--调用示例
--测试数据
declare @UserItem table(UserItemID int,CateName varchar(50),OtherDemo text)
insert @UserItem
select 1,'张三','50&黄石市小河镇&0762-2262626' union all
select 2,'李四','35&广州市&020-2262626' union all
select 3,'博士','25&青岛&0456-2262626' union all
select 4,'学士','25&北京&010-2262626'

--分拆
select UserItemID,CateName
,年龄=dbo.f_GetStr(OtherDemo,1)
,地址=dbo.f_GetStr(OtherDemo,2)
,联系电话=dbo.f_GetStr(OtherDemo,3)
from @UserItem

/*--结果

UserItemID CateName 年龄 地址 联系电话
----------- ------------ ---------- ------------- ---------------
1 张三 50 黄石市小河镇 0762-2262626
2 李四 35 广州市 020-2262626
3 博士 25 青岛 0456-2262626
4 学士 25 北京 010-2262626

（所影响的行数为 4 行）
--*/
--*/
CREATE FUNCTION f_GetStr(
@s ntext, --字符串列表
@pos int --要获取的数据信息段
)RETURNS nvarchar(4000)
AS
BEGIN
IF @s IS NULL RETURN(NULL)
DECLARE @s1 nvarchar(4000),@i int,@j int
SELECT @i=1,@j=PATINDEX('%&%',@s)
WHILE @pos>1 AND @j>0
SELECT @pos=@pos-1,
@j=PATINDEX('%&%',SUBSTRING(@s,@i,4000))
,@i=@i+@j
RETURN(SUBSTRING(@s,@i,PATINDEX('%&%',SUBSTRING(@s,@i,4000)+'&')-1))
END
GO