有
test1表有两个字段ID,Rule
ID Rule
1001 abcde
1002 fghij
1003 klmnotest2表有三个字段ID,Code,Name
ID Code Name
1001 bc ZLB
1002 ij ZSH
1003 lm LA
问:test1.Rule字段中含有test2.Code的内容,test1.ID 可以与test2.ID join
怎样才能把test1.Rule中含有的test2.Code用test2.Name代替?
test1表有两个字段ID,Rule
ID Rule
1001 abcde
1002 fghij
1003 klmnotest2表有三个字段ID,Code,Name
ID Code Name
1001 bc ZLB
1002 ij ZSH
1003 lm LA
问:test1.Rule字段中含有test2.Code的内容,test1.ID 可以与test2.ID join
怎样才能把test1.Rule中含有的test2.Code用test2.Name代替?
Where t1.id = t2.id and t1.Rule = t2.Code
create table test1(ID int, [Rule] varchar(20))
insert test1 select 1001, 'abcde'
union all select 1002, 'fghij'
union all select 1003, 'klmno'create table test2(ID int, Code varchar(20), Name varchar(20))
insert test2 select 1001, 'bc', 'ZLB'
union all select 1002, 'ij', 'ZSH'
union all select 1003, 'lm', 'LA'
update test1 set [Rule]=replace([Rule], test2.Code, test2.Name)
from test2
where test1.ID=test2.IDselect * from test1
--result
ID Rule
----------- --------------------
1001 aZLBde
1002 fghZSH
1003 kLAno(3 row(s) affected)
where test1.id =test2.id and CHARINDEX(test2.code,test1.[rule]) >0
而上面的循环写得也有问题,它只支持含有2个code的表