请教关于街路巷问题的解法

[frank_zu]

近日 遇到有关街路巷的问题
具体是
表中有两个字段：code，name，
code ：记录编码，char（6），街的编码类似‘__0000’,路的编码类似'____00'，巷的编码类似'______'
例如
----------------------------------------------
name code
----------------------------------------------
南京路 '210000'
步行街 （南京路） '211200'
回马巷（南京路步行街） '211201'
-----------------------------------------------
name： 记录了城市街道的名称（名称没有 详细处理，其中括号的内容不存在 ）
现在 想处理这个name，让名称具体，比如上面的回马巷 处理后是 南京路步行街回马巷，步行街成为南京路步行街，
请教，最快的处理方法
[e-mail]

[huailairen]

create table mytable(name varchar(100), code varchar(6))
insert into mytable select
'南京路', '210000' union select
'步行街' , '211200' union select
'回马巷', '211201'

select c.name+b.name +a.name
from mytable a join mytable b on (substring(a.code,3,2)=substring(b.code,3,2) and right(b.code,2)='00')
join mytable c on(right(c.code,4)='0000' and substring(a.code,1,2)=substring(c.code,1,2))
where right(a.code,2)<>'00'

结果

-------------------------------------------------------------------------------------
南京路步行街回马巷

（所影响的行数为 1 行）

[huailairen]

select [name]
from mytable
where right(code,4)='0000'
union all
select b.name+a.name
from mytable a join mytable b on (substring(a.code,3,2)=substring(b.code,3,2) and right(b.code,2)='00')
where right(a.code,2)<>'00'
union all
select c.name+b.name +a.name
from mytable a join mytable b on (substring(a.code,3,2)=substring(b.code,3,2) and right(b.code,2)='00')
join mytable c on(right(c.code,4)='0000' and substring(a.code,1,2)=substring(c.code,1,2))
where right(a.code,2)<>'00'

[huailairen]

name
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
南京路
步行街回马巷
南京路步行街回马巷

（所影响的行数为 3 行）

[frank_zu]

谢谢，流浪猫，
好像 ，我没有表达清楚，我是要把表里的name字段更改，而不是，拼接显示啊

[十一月猪]

create table t_load
( name varchar(100) , code varchar(1000))

insert into t_load
select '南京路', '210000' union
select '步行街', '211200' union
select '回马巷', '211201'


declare @code varchar(8000)
declare @code_comp varchar(8000)
declare @name varchar(8000)
select @code = ''
select @code_comp = ''
select @name = ''


declare cursor1 scroll cursor for select code , name from t_load
open cursor1

fetch next from cursor1 into @code ,@name

while @@fetch_status = 0
begin
select @code_comp = substring(@code,3,4)
if @code_comp <> '0000'
begin
select @code_comp = substring(@code,5,2)
print @code_comp

select @name = @name +'('+ substring(name,1,3) + ')'
from t_load
where code = substring(@code,1,2) + '0000'
print @name

if @code_comp = '00'

begin

update t_load
set name = @name
where code = @code
end

else

begin
select @name = substring(@name,1,len(@name) - 1) +substring(name,1,3) + ')'
from t_load
where code = substring(@code,1,4) + '00'

update t_load
set name = @name
where code = @code
end

end

fetch next from cursor1 into @code , @name

end

close cursor1
deallocate cursor1

select * from t_load