假设一个table如下结构
id(int) member(varchar)
1 jacky;mandy;amy
2
3 wendy;herry
如何将member的string split开来，然后返回下面的结构
1 jacky
1 mandy
1 amy
3 wendy
3 herry
2由于无内容，过滤掉
谢谢各位了！
------解决方案--------------------
- SQL code
--分拆列值--原著：邹建--改编：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-12-16 广东深圳/*有表tb, 如下: id value ----------- ----------- 1 aa,bb 2 aaa,bbb,ccc */--欲按id,分拆value列, 分拆后结果如下: /*id value ----------- -------- 1 aa 1 bb 2 aaa 2 bbb 2 ccc */--1. 旧的解决方法(sql server 2000) select top 8000 id = identity(int, 1, 1) into # from syscolumns a, syscolumns b select A.id, substring(A.[values], B.id, charindex(',', A.[values] + ',', B.id) - B.id) from tb A, # B where substring(',' + A.[values], B.id, 1) = ',' drop table # --2. 新的解决方法(sql server 2005) create table tb(id int,value varchar(30)) insert into tb values(1,'aa,bb') insert into tb values(2,'aaa,bbb,ccc') go select A.id, B.value from( select id, [value] = convert(xml,' <root> <v>' + replace([value], ',', ' </v> <v>') + ' </v> </root>') from tb )A outer apply( select value = N.v.value('.', 'varchar(100)') from A.[value].nodes('/root/v') N(v) )B drop table tb /* id value ----------- ------------------------------ 1 aa 1 bb 2 aaa 2 bbb 2 ccc (5 行受影响) */
------解决方案--------------------
- SQL code
create table mac(id int, member varchar(60))insert into macselect 1, 'jacky;mandy;amy' union allselect 2, '' union all select 3, 'wendy;herry'select a.id,substring(a.member+';',b.number,charindex(';',a.member+';',b.number)-b.number) 'member'from mac ainner join master.dbo.spt_values bon b.[type]='P' and substring(';'+a.member,b.number,1)=';'where a.member<>''id member----------- ---------1 jacky1 mandy1 amy3 wendy3 herry