超难解决,地址标准化问题

Question

找关系差匹配字，然后将结果先select出来看是否正确，
正确后，再将select语句改为update语句。

Answer 1

create table t1(address varchar(100))
insert into t1 select '广州市海珠区皇后大道中'
insert into t1 select '广州市棠下区先烈路'create table t2(address varchar(100))
insert into t2 select '皇后大道中32号205房'
insert into t2 select '先烈中路314号'set rowcount 100
select identity(int,1,1) as id into #t from sysobjects
set rowcount 0select
    id,
    b.address+stuff(c.address,1,a.id,'')
from
    #t a,
    t1 b,
    t2 c
where
    a.id<=len(b.address) and a.id<=len(c.address)
    and
    right(b.address,a.id)=left(c.address,a.id)drop table t1,t2,#t

Answer 2

create table t1(address varchar(100))
insert into t1 select '广州市海珠区皇后大道中'
insert into t1 select '广州市棠下区先烈路'create table t2(address varchar(100))
insert into t2 select '皇后大道中32号205房'
insert into t2 select '先烈中路314号'set rowcount 100
select identity(int,1,1) as id into #t from sysobjects
set rowcount 0update d
set
    address = e.newaddress
from
    t2 d,
    (select
         c.address,
         b.address+stuff(c.address,1,a.id,'') as newaddress
     from
         #t a,
         t1 b,
         t2 c
     where
         a.id<=len(b.address) and a.id<=len(c.address)
         and
         right(b.address,a.id)=left(c.address,a.id)) e
where
    d.address = e.addressselect * from t2drop table t1,t2,#t