Using a wildcard in an update?

I want to go through a table, and change开发者_JAVA百科 all instances of 'notify4-N' to 'notify5-N', where N is a number from 1-9. Is there a way to do that in SQL? It would be easy in perl, but I'm not sure the customer even has perl on his servers.

You are probably looking for the REGEXP_REPLACE and REGEXP_LIKE function in conjunction with an update.

 update sometable set somecol = REGEXP_REPLACE(somecol, ...) where REGEXP_LIKE(somecol, ...) 

This shows the values that would be used in the update. The where condition ensures that notify4-11 is left unchanged.

create table notify(n varchar(20));

insert into notify(n) values('notify4-0');
insert into notify(n) values('notify4-1');
insert into notify(n) values('notify4-2');
insert into notify(n) values('notify4-8');
insert into notify(n) values('notify4-9');
insert into notify(n) values('notify4-11');
select n, regexp_replace(n,'^notify4-([1-9]{1})$', 'notify5-\1') from notify where regexp_like(n, '^notify4-[1-9]{1}$') order by n;

Not tested, but:

UPDATE my_table SET name = 'notify5-' || SUBSTR(name, 9) WHERE name LIKE 'notify4-%'

This should work for database servers that don't support regex matching. :-) (But, I see your post is tagged with Oracle, so, I presume Klaus's answer will work for you too.)