Have Geek Brain

Using a template in an update?

I want to go through the table and change all instances of "notify4-N" to "notify5-N", where N is a number between 1 and 9. Is there a way to do this in SQL? This would be easy in perl, but I'm not sure if the client even has perl on their servers.

+2


a source to share


3 answers


You may be looking for a feature REGEXP_REPLACE

and REGEXP_LIKE

in combination with an update.



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


a source


The values ​​that will be used in the update are displayed here. The where clause ensures that notify4-11 remains 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;
+2


a source


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 by Oracle, so I suppose Klaus's answer will work for you too.)

+1


a source






More articles:


All Articles