34,590
社区成员
发帖
与我相关
我的任务
分享
create table tb(IR_SID int,IR_VRESERVED2 nvarchar(20),IR_URLTITLE nvarchar(20), IR_SRCNAME nvarchar(50))
insert into tb values(1,'a','GB 14907-2002','GB 14907-1994')
insert into tb values(2,'b','GB 14723-2008','GB 14723-1994;GB 12109-1994')
insert into tb values(3,'a','GB 14883.4-1994','')
go
--插入IR_URLTITLE列
select * , col = '' from tb where IR_SID not in (
select IR_SID from tb where (charindex('-',IR_URLTITLE) > 0 and charindex('.',IR_URLTITLE) = 0) and charindex(substring(IR_URLTITLE,1,charindex('-',IR_URLTITLE)-1),IR_SRCNAME) > 0
union all
select IR_SID from tb where (charindex('.',IR_URLTITLE) > 0) and charindex(substring(IR_URLTITLE,1,charindex('.',IR_URLTITLE)-1),IR_SRCNAME) > 0
)
union all
select * , col = IR_URLTITLE from tb where (charindex('-',IR_URLTITLE) > 0 and charindex('.',IR_URLTITLE) = 0) and charindex(substring(IR_URLTITLE,1,charindex('-',IR_URLTITLE)-1),IR_SRCNAME) > 0
union all
select * , col = IR_URLTITLE from tb where (charindex('.',IR_URLTITLE) > 0) and charindex(substring(IR_URLTITLE,1,charindex('.',IR_URLTITLE)-1),IR_SRCNAME) > 0
order by ir_sid
/*
IR_SID IR_VRESERVED2 IR_URLTITLE IR_SRCNAME col
----------- -------------------- -------------------- -------------------------------------------------- --------------------
1 a GB 14907-2002 GB 14907-1994 GB 14907-2002
2 b GB 14723-2008 GB 14723-1994;GB 12109-1994 GB 14723-2008
3 a GB 14883.4-1994
(所影响的行数为 3 行)
*/
--插入IR_SRCNAME
select * , col = '' from tb where IR_SID not in (
select IR_SID from
(
select b.IR_SID,b.IR_VRESERVED2,b.IR_URLTITLE,IR_SRCNAME=substring(b.IR_SRCNAME, number, charindex(';', b.IR_SRCNAME + ';', number) - number)
from master..spt_values a,tb b
where type='p' and substring(';' + b.IR_SRCNAME, number,1) = ';'
) t
where (charindex('-',IR_URLTITLE) > 0 and charindex('.',IR_URLTITLE) = 0) and charindex(substring(IR_URLTITLE,1,charindex('-',IR_URLTITLE)-1),IR_SRCNAME) > 0
union all
select IR_SID from
(
select b.IR_SID,b.IR_VRESERVED2,b.IR_URLTITLE,IR_SRCNAME=substring(b.IR_SRCNAME, number, charindex(';', b.IR_SRCNAME + ';', number) - number)
from master..spt_values a,tb b
where type='p' and substring(';' + b.IR_SRCNAME, number,1) = ';'
) t
where (charindex('.',IR_URLTITLE) > 0) and charindex(substring(IR_URLTITLE,1,charindex('.',IR_URLTITLE)-1),IR_SRCNAME) > 0
)
union all
select * , col = IR_SRCNAME from
(
select b.IR_SID,b.IR_VRESERVED2,b.IR_URLTITLE,IR_SRCNAME=substring(b.IR_SRCNAME, number, charindex(';', b.IR_SRCNAME + ';', number) - number)
from master..spt_values a,tb b
where type='p' and substring(';' + b.IR_SRCNAME, number,1) = ';'
) t
where (charindex('-',IR_URLTITLE) > 0 and charindex('.',IR_URLTITLE) = 0) and charindex(substring(IR_URLTITLE,1,charindex('-',IR_URLTITLE)-1),IR_SRCNAME) > 0
union all
select * , col = IR_SRCNAME from
(
select b.IR_SID,b.IR_VRESERVED2,b.IR_URLTITLE,IR_SRCNAME=substring(b.IR_SRCNAME, number, charindex(';', b.IR_SRCNAME + ';', number) - number)
from master..spt_values a,tb b
where type='p' and substring(';' + b.IR_SRCNAME, number,1) = ';'
) t
where (charindex('.',IR_URLTITLE) > 0) and charindex(substring(IR_URLTITLE,1,charindex('.',IR_URLTITLE)-1),IR_SRCNAME) > 0
order by ir_sid
/*
IR_SID IR_VRESERVED2 IR_URLTITLE IR_SRCNAME col
----------- -------------------- -------------------- -------------------------------------------------- --------------------------------------------------
1 a GB 14907-2002 GB 14907-1994 GB 14907-1994
2 b GB 14723-2008 GB 14723-1994 GB 14723-1994
3 a GB 14883.4-1994
(所影响的行数为 3 行)
*/
drop table tb
create table tb(IR_SID int,IR_VRESERVED2 nvarchar(20),IR_URLTITLE nvarchar(20), IR_SRCNAME nvarchar(50))
insert into tb values(1,'a','GB 14907-2002','GB 14907-1994')
insert into tb values(2,'b','GB 14723-2008','GB 14723-1994;GB 12109-1994')
insert into tb values(3,'a','GB 14883.4-1994','')
go
--插入IR_URLTITLE列
select * , col = IR_URLTITLE from tb where (charindex('-',IR_URLTITLE) > 0 and charindex('.',IR_URLTITLE) = 0) and charindex(substring(IR_URLTITLE,1,charindex('-',IR_URLTITLE)-1),IR_SRCNAME) > 0
union all
select * , col = IR_URLTITLE from tb where (charindex('.',IR_URLTITLE) > 0) and charindex(substring(IR_URLTITLE,1,charindex('.',IR_URLTITLE)-1),IR_SRCNAME) > 0
/*
IR_SID IR_VRESERVED2 IR_URLTITLE IR_SRCNAME col
----------- -------------------- -------------------- -------------------------------------------------- --------------------
1 a GB 14907-2002 GB 14907-1994 GB 14907-2002
2 b GB 14723-2008 GB 14723-1994;GB 12109-1994 GB 14723-2008
(所影响的行数为 2 行)
*/
--IR_SRCNAME
select * , col = IR_SRCNAME from
(
select b.IR_SID,b.IR_VRESERVED2,b.IR_URLTITLE,IR_SRCNAME=substring(b.IR_SRCNAME, number, charindex(';', b.IR_SRCNAME + ';', number) - number)
from master..spt_values a,tb b
where type='p' and substring(';' + b.IR_SRCNAME, number,1) = ';'
) t
where (charindex('-',IR_URLTITLE) > 0 and charindex('.',IR_URLTITLE) = 0) and charindex(substring(IR_URLTITLE,1,charindex('-',IR_URLTITLE)-1),IR_SRCNAME) > 0
union all
select * , col = IR_SRCNAME from
(
select b.IR_SID,b.IR_VRESERVED2,b.IR_URLTITLE,IR_SRCNAME=substring(b.IR_SRCNAME, number, charindex(';', b.IR_SRCNAME + ';', number) - number)
from master..spt_values a,tb b
where type='p' and substring(';' + b.IR_SRCNAME, number,1) = ';'
) t
where (charindex('.',IR_URLTITLE) > 0) and charindex(substring(IR_URLTITLE,1,charindex('.',IR_URLTITLE)-1),IR_SRCNAME) > 0
/*
IR_SID IR_VRESERVED2 IR_URLTITLE IR_SRCNAME col
----------- -------------------- -------------------- -------------------------------------------------- --------------------------------------------------
1 a GB 14907-2002 GB 14907-1994 GB 14907-1994
2 b GB 14723-2008 GB 14723-1994 GB 14723-1994
(所影响的行数为 2 行)
*/
drop table tb
create table tb(IR_SID int,IR_VRESERVED2 nvarchar(20),IR_URLTITLE nvarchar(20), IR_SRCNAME nvarchar(50))
insert into tb values(1,'a','GB 14907-2002','GB 14907-1994')
insert into tb values(2,'b','GB 14723-2008','GB 14723-1994;GB 12109-1994')
insert into tb values(3,'a','GB 14883.4-1994','')
go
--插入IR_URLTITLE列
select * , col = IR_URLTITLE from tb where (charindex('-',IR_URLTITLE) > 0 and charindex('.',IR_URLTITLE) = 0) and charindex(substring(IR_URLTITLE,1,charindex('-',IR_URLTITLE)-1),IR_SRCNAME) > 0
union all
select * , col = IR_URLTITLE from tb where (charindex('.',IR_URLTITLE) > 0) and charindex(substring(IR_URLTITLE,1,charindex('.',IR_URLTITLE)-1),IR_SRCNAME) > 0
drop table tb
/*
IR_SID IR_VRESERVED2 IR_URLTITLE IR_SRCNAME col
----------- -------------------- -------------------- -------------------------------------------------- --------------------
1 a GB 14907-2002 GB 14907-1994 GB 14907-2002
2 b GB 14723-2008 GB 14723-1994;GB 12109-1994 GB 14723-2008
(所影响的行数为 2 行)
*/