地址匹配问题，请大家帮忙指导

[huangyj]

表A
ID Address ZipCode
1 北京东路101号
2 北京东路201号
3 北京东路100号
4 北京东路202号

表B
Address iBegin iEnd Zipcode 备注
北京东路 100 200 200100 双号
北京东路 202 400 200200 双号
北京东路 101 199 200300 单号
北京东路 201 399 200400 单号

现在需要根据表B的地址、门牌岂止范围所对应的邮政编码，更新表A中对应的邮政编码列的信息。

谢谢

[AcHerat]

update a
set zipcode = (select zipcode
               from b
               where substring(a.address,5,3) between ibegin and iend)

[dawugui]

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

[AcHerat]

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 行受影响)

[dawugui]

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

[叶子]

一个范围的号，被更新成一个了，岂不是有重号了？

[huangyj]

对于门牌号码的位数不一样的可以吗？
比如门牌开始 为 1，结束位为101

[dawugui]

--楼主貌似还分单双号的?

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

[dawugui]

[Quote=引用 6 楼 huangyj 的回复:]
对于门牌号码的位数不一样的可以吗？
比如门牌开始 为 1，结束位为101
[/Quote]如果固定的位数的用substring,如果不固定的,用函数,在7楼两种方法都有.

[AcHerat]

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 行受影响)

[huangyj]

表A
ID Address ZipCode
1 北京东路13号
2 北京东路201号
3 北京东路8号
4 北京东路202号

表B
Address iBegin iEnd Zipcode 备注
北京东路 2 200 200100 双号
北京东路 202 400 200200 双号
北京东路 5 199 200300 单号
北京东路 201 399 200400 单号

[AcHerat]

9# 里的select 后去掉 top 1 结果也是一样的。

[AcHerat]

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 行受影响)

[dawugui]

[Quote=引用 10 楼 huangyj 的回复:]
表A
ID Address ZipCode
1 北京东路13号
2 北京东路201号
3 北京东路8号
4 北京东路202号

表B
Address iBegin iEnd Zipcode 备注
北京东路 2 200 200100 双号
北京东路 202 400 200200 双号
北京东路 5 199 200300 单号
北京东路 201 399 200400 单……
[/Quote]参考7楼第一种方法.

[gw6328]

借用楼上的表，
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位的门牌。要多位，回家再想想看。现在忙。

[gw6328]

7楼一种啊还可以用正则啊。顶起