巧妙利用连表查询拆分字符串

我有一个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 bselect a.iName, iNewDesc=substring(a.iDesc,b.id,charindex('/',a.iDesc+'/',b.id)-b.id)from Str_Test a, # bwhere substring('/'+a.iDesc,b.id,1)='/'order by a.iName,b.id

drop table #

--查询结果

iName  iNewDesc  ---------  --------------A      AGFA      dfghB      rtyB      fghjkB      uytB      fghjF      rtyF      ghjF      cvbH      uioH      vbnH      4567H      678