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;

Answer 3

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