update t1 set record = replace(recode,'一批',''); go update t1 set record = replace(recode,'二批',''); go update t1 set record = replace(recode,'三批','');
update t1 set record = REPLACE(REPLACE(replace(recode,'一批',''),'二批',''),'三批','');
create table mytest ( record nvarchar(50) )insert into mytest (record) values ('95三批二次') insert into mytest (record) values ('城东二批') insert into mytest (record) values ('城西') insert into mytest (record) values ('100') insert into mytest (record) values ('津南') insert into mytest (record) values ('东城四批')SELECT * FROM mytestselect PATINDEX('%[一|二|三|四|五|六|七|八|九|十][批]%',record ) FROM mytest update mytest set record = replace(record,SUBSTRING(record,PATINDEX('%[一|二|三|四|五|六|七|八|九|十][批]%',record ),2),'') where record like '%[一|二|三|四|五|六|七|八|九|十][批]%' /* 95二次 城东 城西 100 津南 东城*/
set record = replace(recode,'一批','');
go
update t1
set record = replace(recode,'二批','');
go
update t1
set record = replace(recode,'三批','');
set record = REPLACE(REPLACE(replace(recode,'一批',''),'二批',''),'三批','');
(
record nvarchar(50)
)insert into mytest (record) values ('95三批二次')
insert into mytest (record) values ('城东二批')
insert into mytest (record) values ('城西')
insert into mytest (record) values ('100')
insert into mytest (record) values ('津南')
insert into mytest (record) values ('东城四批')SELECT * FROM mytestselect PATINDEX('%[一|二|三|四|五|六|七|八|九|十][批]%',record ) FROM mytest update mytest set record = replace(record,SUBSTRING(record,PATINDEX('%[一|二|三|四|五|六|七|八|九|十][批]%',record ),2),'')
where record like '%[一|二|三|四|五|六|七|八|九|十][批]%'
/*
95二次
城东
城西
100
津南
东城*/