这样就行了 create table ta(c varchar(100)) insert ta select '123156465' select 'z1231564564%*' update ta set c=replace(c,'%*','%') where right(c,len(c)-2) like'%!%!*' ESCAPE '!' c ---------------------------------------------------------------------------------------------------- z1231564564% 123156465(所影响的行数为 2 行)
如果*不是位于最后,可以这样: create Table Tb(C varchar(20)) insert Tb(C) select '1234*567*89' union all select 'abcd*efg*hi' union all select 'ABCF*EER*' select C,CS=reverse(left(reverse(C),charindex('*',reverse(C))-1)+right(reverse(C),len(C)-charindex('*',reverse(C)))) from Tb drop table Tb ---结果 C CS -------------------- ---------------------------------------- 1234*567*89 1234*56789 abcd*efg*hi abcd*efghi ABCF*EER* ABCF*EER
create table ta(c varchar(100)) insert ta select '12315%*6465%*' union all select '123156465' union all select 'z1231564564%*'update ta set c=stuff(c,len(c),1,'') where right(c,1)='*'select * from ta c ---------------------------------------------------------------------------------------------------- 12315%*6465% 123156465 z1231564564%(所影响的行数为 3 行)
create table ta(c varchar(100))
insert ta
select '123156465'
select 'z1231564564%*'
update ta set c=replace(c,'%*','%')
where right(c,len(c)-2) like'%!%!*' ESCAPE '!'
c
----------------------------------------------------------------------------------------------------
z1231564564%
123156465(所影响的行数为 2 行)
create Table Tb(C varchar(20))
insert Tb(C)
select '1234*567*89'
union all select 'abcd*efg*hi'
union all select 'ABCF*EER*'
select C,CS=reverse(left(reverse(C),charindex('*',reverse(C))-1)+right(reverse(C),len(C)-charindex('*',reverse(C)))) from Tb
drop table Tb
---结果
C CS
-------------------- ----------------------------------------
1234*567*89 1234*56789
abcd*efg*hi abcd*efghi
ABCF*EER* ABCF*EER
insert ta
select '12315%*6465%*' union all
select '123156465' union all
select 'z1231564564%*'update ta
set c=stuff(c,len(c),1,'')
where right(c,1)='*'select * from ta
c
----------------------------------------------------------------------------------------------------
12315%*6465%
123156465
z1231564564%(所影响的行数为 3 行)