拆分字符串实例--> 测试数据: #T if object_id('tempdb.dbo.#T') is not null drop table #T create table #T (id int,name varchar(8)) insert into #T select 1,'jame,job' union all select 2,'paul,mc' union all select 3,'carl';--> 1. CTE 递归找分隔字符位置法:速度极快 with T (id,P1,P2) as ( select id,charindex(',',','+name),charindex(',',name+',')+1 from #T union all select a.id,b.P2,charindex(',',name+',',b.P2)+1 from #T a join T b on a.id=b.id where charindex(',',name+',',b.P2)>0 ) select a.id,name=substring(a.name+',',b.P1,b.P2 - b.P1 - 1) from #T a join T b on a.id=b.id order by 1 /* id name ----------- --------- 1 jame 1 job 2 mc 2 paul 3 carl */ --> 测试数据: #T if object_id('tempdb.dbo.#T') is not null drop table #T create table #T (id int,name varchar(8)) insert into #T select 1,'jame,job' union all select 2,'paul,mc' union all select 3,'carl';--> 2. 临时表法:速度比CTE方法相差无几 if object_id('tempdb.dbo.#') is not null drop table # select top 8000 id=identity(int,1,1) into # from syscolumns,sysobjects --> select max(len(name)) from #T=11 select a.id,name=substring(a.name+',',b.id,charindex(',',a.name+',',b.id+1)-b.id) from #T a inner join # b on substring(','+a.name,b.id,1)=',' /* id name ----------- --------- 1 jame 1 job 2 mc 2 paul 3 carl */3. XML法:速度较慢 select a.id,b.name from (select id,name=convert(xml,'<root><v>'+replace(name,',','</v><v>')+'</v></root>') from #T ) a outer apply (select name=N.v.value('.','varchar(100)') from a.name.nodes('/root/v') N(v)) b /* id name ----------- --------- 1 jame 1 job 2 mc 2 paul 3 carl */ 分拆列值 原著:邹建 改编:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 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 行受影响) */
典型的拆分列,样例见楼上。 不过看LZ的数据,如果第二个字段是标准的 AA、BB的形式。 --> 测试数据:[TB] if object_id('[TB]') is not null drop table [TB] create table [TB]([ID] int,[书名] varchar(4),[作者] varchar(10)) insert [TB] select 1,'XXX1','张三、李四' union all select 2,'XXX2','王武、张三'select * from [TB]WITH TT AS( SELECT ID,[书名],[作者] = SUBSTRING([作者],0,CHARINDEX('、',[作者])) FROM TB UNION ALL SELECT ID,[书名],[作者] = SUBSTRING([作者],CHARINDEX('、',[作者])+1,LEN([作者])) FROM TB)SELECT [作者],[书名] FROM TT ORDER BY [作者] DESC /* 作者 书名 张三 XXX1 张三 XXX2 王武 XXX2 李四 XXX1*/--更多分类还请LZ考虑处理。
SELECT 书名,'张三' AS 作者 FROM tb22 WHERE 作者 LIKE '%张三%' UNION ALL SELECT 书名,'李四' AS 作者 FROM tb22 WHERE 作者 LIKE '%李四%' UNION ALL SELECT 书名,'王武' AS 作者 FROM tb22 WHERE 作者 LIKE '%王武%' -----结果 书名 作者 一 张三 二 张三 一 李四 二 王武
改进了一下! SELECT 书名,LEFT(作者,PATINDEX('%、%',作者)-1) AS 作者 FROM tb22 UNION ALL SELECT 书名,RIGHT(作者,PATINDEX('%、%',作者)-1) AS 作者 FROM tb22
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (id int,name varchar(8))
insert into #T
select 1,'jame,job' union all
select 2,'paul,mc' union all
select 3,'carl';--> 1. CTE 递归找分隔字符位置法:速度极快
with T (id,P1,P2) as
(
select id,charindex(',',','+name),charindex(',',name+',')+1 from #T
union all
select a.id,b.P2,charindex(',',name+',',b.P2)+1 from #T a join T b on a.id=b.id where charindex(',',name+',',b.P2)>0
)
select a.id,name=substring(a.name+',',b.P1,b.P2 - b.P1 - 1) from #T a join T b on a.id=b.id order by 1
/*
id name
----------- ---------
1 jame
1 job
2 mc
2 paul
3 carl
*/
--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (id int,name varchar(8))
insert into #T
select 1,'jame,job' union all
select 2,'paul,mc' union all
select 3,'carl';--> 2. 临时表法:速度比CTE方法相差无几
if object_id('tempdb.dbo.#') is not null drop table #
select top 8000 id=identity(int,1,1) into # from syscolumns,sysobjects --> select max(len(name)) from #T=11
select a.id,name=substring(a.name+',',b.id,charindex(',',a.name+',',b.id+1)-b.id) from #T a inner join # b on substring(','+a.name,b.id,1)=','
/*
id name
----------- ---------
1 jame
1 job
2 mc
2 paul
3 carl
*/3. XML法:速度较慢
select
a.id,b.name
from
(select id,name=convert(xml,'<root><v>'+replace(name,',','</v><v>')+'</v></root>') from #T ) a
outer apply
(select name=N.v.value('.','varchar(100)') from a.name.nodes('/root/v') N(v)) b
/*
id name
----------- ---------
1 jame
1 job
2 mc
2 paul
3 carl
*/
分拆列值 原著:邹建
改编:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 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 行受影响)
*/
不过看LZ的数据,如果第二个字段是标准的 AA、BB的形式。
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([ID] int,[书名] varchar(4),[作者] varchar(10))
insert [TB]
select 1,'XXX1','张三、李四' union all
select 2,'XXX2','王武、张三'select * from [TB]WITH TT
AS(
SELECT ID,[书名],[作者] = SUBSTRING([作者],0,CHARINDEX('、',[作者]))
FROM TB
UNION ALL
SELECT ID,[书名],[作者] = SUBSTRING([作者],CHARINDEX('、',[作者])+1,LEN([作者]))
FROM TB)SELECT [作者],[书名]
FROM TT
ORDER BY [作者] DESC
/*
作者 书名
张三 XXX1
张三 XXX2
王武 XXX2
李四 XXX1*/--更多分类还请LZ考虑处理。
SELECT 书名,'张三' AS 作者 FROM tb22 WHERE 作者 LIKE '%张三%'
UNION ALL
SELECT 书名,'李四' AS 作者 FROM tb22 WHERE 作者 LIKE '%李四%'
UNION ALL
SELECT 书名,'王武' AS 作者 FROM tb22 WHERE 作者 LIKE '%王武%'
-----结果
书名 作者
一 张三
二 张三
一 李四
二 王武
SELECT 书名,LEFT(作者,PATINDEX('%、%',作者)-1) AS 作者 FROM tb22
UNION ALL
SELECT 书名,RIGHT(作者,PATINDEX('%、%',作者)-1) AS 作者 FROM tb22