字符串的拆分

我有一个Table myTable (iName varchar(6),iDesc varchar(2000))
内容示例如下:
iName                   iDesc
A                       AGF/dfgh
B                       rty/fghjk/uyt/fghj
F                       rty/ghj/cvb
H                       uio/vbn/4567/678
...
即字段iDesc的内容由'/'字符分割,先想得到下面的结果:
iName                   iNewDesc
A                       AFG
A                       dfgh
B                       rty
B                       fghjk
B                       uyt
...

--以往解决方法,写一个函数然后循环拆,效率低

--先提供一个巧妙利用连表查询解决方法,如下:

--创建测试环境

Create Table Str_Test (iName varchar(6),iDesc varchar(2000))
insert into Str_Test select 'A','AGF/dfgh'
union all select 'B','rty/fghjk/uyt/fghj'
union all select 'F','rty/ghj/cvb'
union all select 'H','uio/vbn/4567/678'

--巧妙连表方法

select top 2000 id=identity(int,1,1) into # from syscolumns a,syscolumns b
select a.iName,
 iNewDesc=substring(a.iDesc,b.id,charindex('/',a.iDesc+'/',b.id)-b.id)
from Str_Test a, # b
where substring('/'+a.iDesc,b.id,1)='/'
order by a.iName,b.id

drop table #

--查询结果

iName  iNewDesc 
---------  --------------
A      AGF
A      dfgh
B      rty
B      fghjk
B      uyt
B      fghj
F      rty
F      ghj
F      cvb
H      uio
H      vbn
H      4567
H      678

--删除测试环境

Drop table Str_Test

 

本文来自CSDN博客http://blog.csdn.net/zlp321002/archive/2005/10/09/498307.aspx