oracle 在更新中使用通配符？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2698747/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using a wildcard in an update?
提问by Paul Tomblin
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.
我想查看一张表，并将“notify4-N”的所有实例更改为“notify5-N”，其中 N 是 1-9 之间的数字。有没有办法在 SQL 中做到这一点？使用 perl 会很容易，但我不确定客户的服务器上是否有 perl。
回答by Klaus Byskov Pedersen
You are probably looking for the REGEXP_REPLACEand REGEXP_LIKEfunction in conjunction with an update.
您可能正在寻找与更新结合使用的REGEXP_REPLACEandREGEXP_LIKE函数。
update sometable set somecol = REGEXP_REPLACE(somecol, ...) where REGEXP_LIKE(somecol, ...)
回答by Janek Bogucki
This shows the values that would be used in the update. The where condition ensures that notify4-11 is left unchanged.
这显示了将在更新中使用的值。where 条件确保notify4-11 保持不变。
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-') from notify where regexp_like(n, '^notify4-[1-9]{1}$') order by n;
回答by Chris Jester-Young
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.)
这应该适用于不支持正则表达式匹配的数据库服务器。:-)（但是，我看到您的帖子被标记为 Oracle，因此，我认为 Klaus 的回答也适用于您。）
