Using a wildcard in an update?

Question

I want to go through a table, and change 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.

Answer 1

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, ...) 

Answer 2

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;