27,579
社区成员
发帖
与我相关
我的任务
分享
借用楼上的表,
update a set ZipCode= b.Zipcode
from a join B on cast(reverse(substring(reverse(a.Address),2,3)) as int) between b.iBegin and iEnd and (cast(reverse(substring(reverse(a.Address),2,3)) as int)%2=0 and b.备注='双号')
or (cast(reverse(substring(reverse(a.Address),2,3)) as int)%2=1 and b.备注='单号')
-- 这个只能匹配3位的门牌。要多位,回家再想想看。现在忙。
update a
set zipcode = (select zipcode
from b
where substring(a.address,5,3) between ibegin and iend
and [备注] = (case when substring(a.address,5,3)%2 > 0 then '单号' else '双号' end))
select * from a
ID Address ZipCode
----------- -------------------- -------
1 北京东路101号 200300
2 北京东路201号 200400
3 北京东路100号 200100
4 北京东路202号 200200
(4 行受影响)
create table a(ID int,Address varchar(20),ZipCode varchar(6))
insert into a values(1 ,'北京东路101号','')
insert into a values(2 ,'北京东路201号','')
insert into a values(3 ,'北京东路100号','')
insert into a values(4 ,'北京东路202号','')
go
create table B(Address varchar(10),iBegin int,iEnd int,Zipcode varchar(10),备注 varchar(10))
insert into b values('北京东路', 100 ,200 ,'200100', '双号')
insert into b values('北京东路', 202 ,400 ,'200200', '双号')
insert into b values('北京东路', 101 ,199 ,'200300', '单号')
insert into b values('北京东路', 201 ,399 ,'200400', '单号')
go
update a
set zipcode = (select top 1 zipcode
from b
where substring(a.address,5,3) between ibegin and iend
and [备注] = (case when substring(a.address,5,3)%2 > 0 then '单号' else '双号' end))
select * from a
drop table a,b
ID Address ZipCode
----------- -------------------- -------
1 北京东路101号 200300
2 北京东路201号 200400
3 北京东路100号 200100
4 北京东路202号 200200
(4 行受影响)
create table a(ID int,Address varchar(20),ZipCode varchar(6))
insert into a values(1 ,'北京东路101号','')
insert into a values(2 ,'北京东路201号','')
insert into a values(3 ,'北京东路100号','')
insert into a values(4 ,'北京东路202号','')
create table B(Address varchar(10),iBegin int,iEnd int,Zipcode varchar(10),备注 varchar(10))
insert into b values('北京东路', 100 ,200 ,'200100', '双号')
insert into b values('北京东路', 202 ,400 ,'200200', '双号')
insert into b values('北京东路', 101 ,199 ,'200300', '单号')
insert into b values('北京东路', 201 ,399 ,'200400', '单号')
go
--建立如下函数(非0-9之外的字符删除)
go
create function getnewstr(@oldstr varchar(20)) returns varchar(20)
as
begin
declare @i int
set @i = 1
while @i <= len(@oldstr)
if substring(@oldstr, @i, 1) like('[^0-9]')
set @oldstr = replace(@oldstr, substring(@oldstr, @i, 1), '')
else
set @i = @i +1
return @oldstr
end
go
update a set ZipCode = b.ZipCode
from a , b
where charindex(b.Address,a.Address) > 0 and
dbo.getnewstr(a.Address) between b.iBegin and b.iEnd
and a.Address like('%[^0-9]%')
and
(
dbo.getnewstr(a.Address) % 2 = 1 and b.备注 = '单号' or
dbo.getnewstr(a.Address) % 2 = 0 and b.备注 = '双号'
)
select * from a
/*
ID Address ZipCode
----------- -------------------- -------
1 北京东路101号 200300
2 北京东路201号 200400
3 北京东路100号 200100
4 北京东路202号 200200
(所影响的行数为 4 行)
*/
drop table a , b
drop function dbo.getnewstr
create table a(ID int,Address varchar(20),ZipCode varchar(6))
insert into a values(1 ,'北京东路101号','')
insert into a values(2 ,'北京东路201号','')
insert into a values(3 ,'北京东路100号','')
insert into a values(4 ,'北京东路202号','')
create table B(Address varchar(10),iBegin int,iEnd int,Zipcode varchar(10),备注 varchar(10))
insert into b values('北京东路', 100 ,200 ,'200100', '双号')
insert into b values('北京东路', 202 ,400 ,'200200', '双号')
insert into b values('北京东路', 101 ,199 ,'200300', '单号')
insert into b values('北京东路', 201 ,399 ,'200400', '单号')
go
update a set ZipCode = b.ZipCode
from a , b
where charindex(b.Address,a.Address) > 0 and substring(a.Address,5,3) between b.iBegin and b.iEnd
and
(
substring(a.Address,5,3) % 2 = 1 and b.备注 = '单号' or
substring(a.Address,5,3) % 2 = 0 and b.备注 = '双号'
)
select * from a
/*
ID Address ZipCode
----------- -------------------- -------
1 北京东路101号 200300
2 北京东路201号 200400
3 北京东路100号 200100
4 北京东路202号 200200
(所影响的行数为 4 行)
*/
drop table a , b
create table a(ID int,Address varchar(20),ZipCode varchar(6))
insert into a values(1 ,'北京东路101号','')
insert into a values(2 ,'北京东路201号','')
insert into a values(3 ,'北京东路100号','')
insert into a values(4 ,'北京东路202号','')
create table B(Address varchar(10),iBegin int,iEnd int,Zipcode varchar(10),备注 varchar(10))
insert into b values('北京东路', 100 ,200 ,'200100', '双号')
insert into b values('北京东路', 202 ,400 ,'200200', '双号')
insert into b values('北京东路', 101 ,199 ,'200300', '单号')
insert into b values('北京东路', 201 ,399 ,'200400', '单号')
go
--建立如下函数(非0-9之外的字符删除)
go
create function getnewstr(@oldstr varchar(20)) returns varchar(20)
as
begin
declare @i int
set @i = 1
while @i <= len(@oldstr)
if substring(@oldstr, @i, 1) like('[^0-9]')
set @oldstr = replace(@oldstr, substring(@oldstr, @i, 1), '')
else
set @i = @i +1
return @oldstr
end
go
update a set ZipCode = b.ZipCode
from a , b
where charindex(b.Address,a.Address) > 0 and
dbo.getnewstr(a.Address) between b.iBegin and b.iEnd
and a.Address like('%[^0-9]%')
select * from a
/*
ID Address ZipCode
----------- -------------------- -------
1 北京东路101号 200100
2 北京东路201号 200400
3 北京东路100号 200100
4 北京东路202号 200200
(所影响的行数为 4 行)
*/
drop table a , b
drop function dbo.getnewstr
create table a(ID int,Address varchar(20),ZipCode varchar(6))
insert into a values(1 ,'北京东路101号','')
insert into a values(2 ,'北京东路201号','')
insert into a values(3 ,'北京东路100号','')
insert into a values(4 ,'北京东路202号','')
go
create table B(Address varchar(10),iBegin int,iEnd int,Zipcode varchar(10),备注 varchar(10))
insert into b values('北京东路', 100 ,200 ,'200100', '双号')
insert into b values('北京东路', 202 ,400 ,'200200', '双号')
insert into b values('北京东路', 101 ,199 ,'200300', '单号')
insert into b values('北京东路', 201 ,399 ,'200400', '单号')
go
update a
set zipcode = (select top 1 zipcode
from b
where substring(a.address,5,3) between ibegin and iend)
select * from a
drop table a,b
ID Address ZipCode
----------- -------------------- -------
1 北京东路101号 200100
2 北京东路201号 200400
3 北京东路100号 200100
4 北京东路202号 200200
(4 行受影响)
create table a(ID int,Address varchar(20),ZipCode varchar(6))
insert into a values(1 ,'北京东路101号','')
insert into a values(2 ,'北京东路201号','')
insert into a values(3 ,'北京东路100号','')
insert into a values(4 ,'北京东路202号','')
create table B(Address varchar(10),iBegin int,iEnd int,Zipcode varchar(10),备注 varchar(10))
insert into b values('北京东路', 100 ,200 ,'200100', '双号')
insert into b values('北京东路', 202 ,400 ,'200200', '双号')
insert into b values('北京东路', 101 ,199 ,'200300', '单号')
insert into b values('北京东路', 201 ,399 ,'200400', '单号')
go
update a set ZipCode = b.ZipCode
from a , b
where charindex(b.Address,a.Address) > 0 and substring(a.Address,5,3) between b.iBegin and b.iEnd
select * from a
/*
ID Address ZipCode
----------- -------------------- -------
1 北京东路101号 200300
2 北京东路201号 200400
3 北京东路100号 200100
4 北京东路202号 200400
(所影响的行数为 4 行)
*/
drop table a , b
update a
set zipcode = (select zipcode
from b
where substring(a.address,5,3) between ibegin and iend)