oracle 在更新中使用 regexp_replace
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14254542/
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 regexp_replace in update
提问by jijo
I have a db column report_name which will have values similar to this
我有一个 db 列 report_name 它将具有与此类似的值
000007091_PaymentRegisterReport _D x3A 975844_2012-12-26.XLS
I need to delete the space before _D in all PaymentRegisterReport with XLS extension. Could somebody help me with the regex to use inside regexp_replace function in the update statement?
我需要删除所有带有 XLS 扩展名的 PaymentRegisterReport 中 _D 之前的空间。有人可以帮助我使用正则表达式在更新语句中使用 regexp_replace 函数吗？
回答by TechDo
Do you really need a regex to update the data? Please check the query.
你真的需要一个正则表达式来更新数据吗？请检查查询。
update TableName
set report_name=REPLACE(report_name, ' _D' , '_D')
WHERE report_name LIKE '%PaymentRegisterReport %' AND
report_name LIKE '%.XLS';
回答by Chetter Hummin
The expression that you need to use is
您需要使用的表达式是
REGEXP_REPLACE(f1, '(.*)(_PaymentRegisterReport) _D (.*)(\.XLS)$', '_D')
I am assuming that you can identify the report type by 'PaymentRegisterReport' and the file extensions will be in uppercase
我假设您可以通过“PaymentRegisterReport”识别报告类型，并且文件扩展名将为大写
回答by Pilou
You can replace the " _D" bi "_D" with a select as said techdo.
您可以将“ _D” bi“ _D”替换为选择作为所述技术。
But I wrote a regex_replace as you was asking :
但是我按照你的要求写了一个 regex_replace ：
select regexp_replace('000007091_PaymentRegisterReport _D x3A 975844_2012-12-26.XLS','^(.*) _D(.*).XLS$','_D.XLS') from dual;
