日期:2014-05-18 浏览次数:20625 次
use tempdb go --测试数据 declare @s varchar(1000) set @s='ak47,mp5,1,23' /*要求输出结果 S ---- ak47 mp5 1 23 */ --3种方法对比: --1.[朴实]动态Exec方法: declare @s1 varchar(1000) set @s1=right(replace(','+@s,',',''' as S union select '''),len(replace(','+@s,',',''' as S union select '''))-12)+'''' exec(@s1) --2.[变通]表交叉方法: select replace(reverse((left(s,charindex(',',s)))),',','') as S from( select r,reverse(left(@s,r))+',' as s from( select (select count(*) from sysobjects where name<=t.name ) as r from sysobjects t )a where r<=len(@s) and left(@s+',',r+1) like '%,' )t order by r --3.[高级]XML方法: DECLARE @idoc int; DECLARE @doc xml; set @doc=cast('<Root><item><S>'+replace(@s,',','</S></item><item><S>')+'</S></item></Root>' as xml) EXEC sp_xml_preparedocument @Idoc OUTPUT, @doc SELECT * FROM OPENXML (@Idoc, '/Root/item',2) WITH ( [S] varchar(10) )
------解决方案--------------------
--分拆列值 --原著:邹建 --改编:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 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 行受影响) */
------解决方案--------------------
create table tb(id int,name varchar(50)) insert into tb select 1,'人民大学' union all select 2,'清华大学' union all select 3,'民族大学、化工大学' union all select 4,'交通大学' union all select 5,'科技大学、地质大学' select distinct substring(a.name,b.number,charindex('、',a.name+'、',b.number+1)-b.number) from tb a,master..spt_values b where b.type='p' and b.number<=len(a.name) and substring(a.name,b.number,1)<>'、' and substring('、'+a.name,b.number,1)='、' /* -------- 地质大学 化工大学 交通大学 科技大学 民族大学 清华大学 人民大学