如何T-用SQL编程实现最终显示3行:
[Xu, Jianguo; Wu, Zai-Sheng; Shen, Weiyu; Xu, Huo; Li, Hongling; Jia, Lee] Fuzhou Univ, Coll Chem, Canc Metastasis Alert & Prevent Ctr, Fuzhou 350002, Peoples R China;
[Xu, Jianguo; Wu, Zai-Sheng; Shen, Weiyu; Xu, Huo; Li, Hongling; Jia, Lee] Fuzhou Univ, Coll Chem, Pharmaceut Photocatalysis, State Key Lab Photocatalysis Energy & Environm, Fuzhou 350002, Peoples R China;
[Wu, Zai-Sheng] Hunan Univ, Coll Chem & Chem Engn, State Key Lab Chemo Biosensing & Chemometr, Changsha 410082, Hunan, Peoples R China
请教高手大牛们, 怎么编程,急急急!非常感谢!急急急!
7 个解决方案
#1
在想换行的地方用回车换行符替换
#2
另外一个就是固定长度加回车换行符
#3
WITH
/* 测试数据
table1(col1)AS(
SELECT '[Xu, Jianguo; ...'
),*/
a AS (
SELECT col1,
1 AS i1,
CHARINDEX('[',col1,2) AS i2
FROM table1
UNION ALL
SELECT col1,
i2 AS i1,
CHARINDEX('[',col1,i2+1) AS i2
FROM a
WHERE i2 <> 0
)
SELECT i1,i2,
SUBSTRING(col1,i1,CASE WHEN i2=0 THEN
LEN(col1)-i1+1
ELSE
i2-i1
END) AS col2
FROM a
i1 i2 col2
----------- ----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 169 [Xu, Jianguo; Wu, Zai-Sheng; Shen, Weiyu; Xu, Huo; Li, Hongling; Jia, Lee] Fuzhou Univ, Coll Chem, Canc Metastasis Alert & Prevent Ctr, Fuzhou 350002, Peoples R China;
169 375 [Xu, Jianguo; Wu, Zai-Sheng; Shen, Weiyu; Xu, Huo; Li, Hongling; Jia, Lee] Fuzhou Univ, Coll Chem, Pharmaceut Photocatalysis, State Key Lab Photocatalysis Energy & Environm, Fuzhou 350002, Peoples R China;
375 0 [Wu, Zai-Sheng] Hunan Univ, Coll Chem & Chem Engn, State Key Lab Chemo Biosensing & Chemometr, Changsha 410082, Hunan, Peoples R China
#4
最终的目的是把分割的三行分别插入到一张表中.字段中的结构的固定的,内容不定,不一定的分割成三行的,有时候可能会分割成2行或4行.怎么实现,应该不能单纯以;作为分隔符,急急急
#5
create table #t1(jingwfw nvarchar(500))
select charindex('[',name,2)pos into #t2 from #t
select 0 as pos into #t3 from #t
while exists (select * from #t2 where pos<>0)
begin
insert into #t1
select SUBSTRING(a.name,c.pos,b.pos-c.pos) from #t a,#t2 b,#t3 c
where b.pos<>0
delete from #t3
insert into #t3 select * from #t2
update a set pos=CHARINDEX('[',b.name+'[',a.pos+1)
from #t2 a,#t b where a.pos<>0
end
select * from #t1
drop table #t1
drop table #t2
drop table #t3
#6
--如果串特别长,可以用函数。
--如果总长2000以内,可参考如下写法:
declare @s varchar(8000)
set @s='[Xu, Jianguo; Wu, Zai-Sheng; Shen, Weiyu; Xu, Huo; Li, Hongling; Jia, Lee] Fuzhou Univ, Coll Chem, Canc Metastasis Alert & Prevent Ctr, Fuzhou 350002, Peoples R China; [Xu, Jianguo; Wu, Zai-Sheng; Shen, Weiyu; Xu, Huo; Li, Hongling; Jia, Lee] Fuzhou Univ, Coll Chem, Pharmaceut Photocatalysis, State Key Lab Photocatalysis Energy & Environm, Fuzhou 350002, Peoples R China; [Wu, Zai-Sheng] Hunan Univ, Coll Chem & Chem Engn, State Key Lab Chemo Biosensing & Chemometr, Changsha 410082, Hunan, Peoples R China'
select s=SUBSTRING(@s,number,charindex('[',@s+'[',number+1)-number)
from master..spt_values
where type='P' and number>0
and charindex('[',@s,number)=number
#7
我#3结果的col2哪里还不符合你的要求?
#1
在想换行的地方用回车换行符替换
#2
另外一个就是固定长度加回车换行符
#3
WITH
/* 测试数据
table1(col1)AS(
SELECT '[Xu, Jianguo; ...'
),*/
a AS (
SELECT col1,
1 AS i1,
CHARINDEX('[',col1,2) AS i2
FROM table1
UNION ALL
SELECT col1,
i2 AS i1,
CHARINDEX('[',col1,i2+1) AS i2
FROM a
WHERE i2 <> 0
)
SELECT i1,i2,
SUBSTRING(col1,i1,CASE WHEN i2=0 THEN
LEN(col1)-i1+1
ELSE
i2-i1
END) AS col2
FROM a
i1 i2 col2
----------- ----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 169 [Xu, Jianguo; Wu, Zai-Sheng; Shen, Weiyu; Xu, Huo; Li, Hongling; Jia, Lee] Fuzhou Univ, Coll Chem, Canc Metastasis Alert & Prevent Ctr, Fuzhou 350002, Peoples R China;
169 375 [Xu, Jianguo; Wu, Zai-Sheng; Shen, Weiyu; Xu, Huo; Li, Hongling; Jia, Lee] Fuzhou Univ, Coll Chem, Pharmaceut Photocatalysis, State Key Lab Photocatalysis Energy & Environm, Fuzhou 350002, Peoples R China;
375 0 [Wu, Zai-Sheng] Hunan Univ, Coll Chem & Chem Engn, State Key Lab Chemo Biosensing & Chemometr, Changsha 410082, Hunan, Peoples R China
#4
最终的目的是把分割的三行分别插入到一张表中.字段中的结构的固定的,内容不定,不一定的分割成三行的,有时候可能会分割成2行或4行.怎么实现,应该不能单纯以;作为分隔符,急急急
#5
create table #t1(jingwfw nvarchar(500))
select charindex('[',name,2)pos into #t2 from #t
select 0 as pos into #t3 from #t
while exists (select * from #t2 where pos<>0)
begin
insert into #t1
select SUBSTRING(a.name,c.pos,b.pos-c.pos) from #t a,#t2 b,#t3 c
where b.pos<>0
delete from #t3
insert into #t3 select * from #t2
update a set pos=CHARINDEX('[',b.name+'[',a.pos+1)
from #t2 a,#t b where a.pos<>0
end
select * from #t1
drop table #t1
drop table #t2
drop table #t3
#6
--如果串特别长,可以用函数。
--如果总长2000以内,可参考如下写法:
declare @s varchar(8000)
set @s='[Xu, Jianguo; Wu, Zai-Sheng; Shen, Weiyu; Xu, Huo; Li, Hongling; Jia, Lee] Fuzhou Univ, Coll Chem, Canc Metastasis Alert & Prevent Ctr, Fuzhou 350002, Peoples R China; [Xu, Jianguo; Wu, Zai-Sheng; Shen, Weiyu; Xu, Huo; Li, Hongling; Jia, Lee] Fuzhou Univ, Coll Chem, Pharmaceut Photocatalysis, State Key Lab Photocatalysis Energy & Environm, Fuzhou 350002, Peoples R China; [Wu, Zai-Sheng] Hunan Univ, Coll Chem & Chem Engn, State Key Lab Chemo Biosensing & Chemometr, Changsha 410082, Hunan, Peoples R China'
select s=SUBSTRING(@s,number,charindex('[',@s+'[',number+1)-number)
from master..spt_values
where type='P' and number>0
and charindex('[',@s,number)=number
#7
我#3结果的col2哪里还不符合你的要求?