22,299
社区成员




declare @t1 table(LCDYZD_GSX varchar(100))
insert @t1
select 'KMJE(-1,0,5402,JFFS,0)' union all
select 'KMJE(0,0,5402,JFLJ,0)' union all
select 'KMJE(-1,0,5102,JFFS,0)-KMJE(-1,0,5405,JFFS,0)' union all
select 'KMJE(-1,0,5502,JFFS,0)' union all
select 'KMJE(0,0,6681021,DFFS,0)'
declare @t2 table(F_YKM int,F_XKM int)
insert @t2
select 5402,1101 union all
select 5102,2301 union all
select 5405,2401 union all
select 5502,1414 union all
select 6681021,2304451
while(@@rowcount>0)
update t1 set LCDYZD_GSX=replace(LCDYZD_GSX,','+rtrim(f_ykm)+',',','+rtrim(f_xkm)+',') from @t1 t1 inner join @t2 t2
on charindex(','+rtrim(f_ykm)+',',','+lcdyzd_gsx+',')>0
select * from @t1
/*
LCDYZD_GSX
----------------------------------------------------------------------------------------------------
KMJE(-1,0,1101,JFFS,0)
KMJE(0,0,1101,JFLJ,0)
KMJE(-1,0,2301,JFFS,0)-KMJE(-1,0,2401,JFFS,0)
KMJE(-1,0,1414,JFFS,0)
KMJE(0,0,2304451,DFFS,0)
(5 row(s) affected)
*/
declare @t1 table(LCDYZD_GSX varchar(100))
insert @t1
select '-1,0,5402,JFFS,0' union all
select '0,0,5402,JFLJ,0' union all
select '-1,0,5102,JFFS,0' union all
select '-1,0,5405,JFFS,0' union all
select '-1,0,5502,JFFS,0' union all
select '0,0,6681021,DFFS,0'
declare @t2 table(F_YKM int,F_XKM int)
insert @t2
select 5402, 1101 union all
select 5102, 2301 union all
select 5502, 1414 union all
select 6681021, 2304451
update t1 set lcdyzd_gsx=replace(lcdyzd_gsx,','+rtrim(f_ykm)+',',','+rtrim(f_xkm)+',')
from @t1 t1 inner join @t2 t2 on charindex(','+rtrim(f_ykm)+',',','+lcdyzd_gsx+',')>0
select * from @t1
/*
LCDYZD_GSX
----------------------------------------------------------------------------------------------------
-1,0,1101,JFFS,0
0,0,1101,JFLJ,0
-1,0,2301,JFFS,0
-1,0,5405,JFFS,0
-1,0,1414,JFFS,0
0,0,2304451,DFFS,0
(6 row(s) affected)
*/
update a
set lcdyzd_gsx = replace(lcdyzd,b.f_ykm,b.f_xkm)
from table1 a
left join table2 on a.主键 = b.主键