create table ta( cname varchar(20), address varchar(20), dm varchar(20)) insert into ta(cname,dm) values('张三','05551101') insert into ta(cname,dm) values('李四','05551102') insert into ta(cname,dm) values('王五','055512') select * from ta gocreate table tb ( address varchar(20), dm varchar(20) )insert into tb(address,dm) values ('中国','0555')insert into tb(address,dm) values ('北京市','055511')insert into tb(address,dm) values ('东城区','05551101')insert into tb(address,dm) values ('西城区','05551102')insert into tb(address,dm) values ('天津市','055512')insert into tb(address,dm) values ('上海市','055513')select top 9 * from tbcreate function fn_add(@dm varchar(16)) returns varchar(40) --declare @dm varchar(16) --set @dm='055511'asbegin declare @addr varchar(40) if len(@dm)=8 begin select @addr= t.address+m.address from tb m , (select b.address+a.address as address,a.dm from tb a , (select * from tb where len(dm)=4 ) b where left(a.dm,4)=b.dm and len(a.dm)=6 ) t where left(@dm,6)=t.dm and len(m.dm)=8 and m.dm=@dm end if len(@dm)=6 begin select @addr= b.address+a.address from tb a , (select * from tb where len(dm)=4 ) b where left(@dm,4)=b.dm and len(a.dm)=6 and a.dm=@dm end if len(@dm) =4 begin select @addr= address from tb where len(@dm)=4 and dm=@dm end return @addr end goselect cname,dbo.fn_add(dm),dm from ta运行结果：张三 中国北京市东城区 05551101李四 中国北京市西城区 05551102王五 中国天津市 055512--来源 ：http://www.delphibbs.com/delphibbs/dispq.asp?lid=3977958drop table tadrop table tbdrop function fn_add
