regex_replace，仅替换整个单词

Question

我的 PostgreSQL 表中有以下行:

kpi_old
---------------------------------
Other operating exp (1200)
Other operating exp (1400)
Other operating exp (1600)
Other operating expenses 0 (1200)
Other operating expenses 0 (1400)
Other operating expenses 0 (1600)

我想替换:

• Other operational exp => Other operations expenses & taxes
• 其他运营费用 0 => 其他运营费用和税金

我尝试了以下方法:

SELECT
"kpi_old",
regexp_replace("kpi_old", '(Other operating exp)','Other operating expenses & taxes') as "kpi_new"

问题是，它返回以下内容:

Other operating expenses & taxes (1200)
Other operating expenses & taxes (1400)
Other operating expenses & taxes (1600)
Other operating expenses & taxesenses 0 (1200)
Other operating expenses & taxesenses 0 (1400)
Other operating expenses & taxesenses 0 (1600)

我可以使用哪个正则表达式来替换字符串中的精确匹配？

Answer 1

使用可选组 (enses)?

=# select regexp_replace('Other operating expenses 0 (1600)', '(Other operating exp(enses)?)', 'Other operating expenses & taxes');
              regexp_replace               
-------------------------------------------
 Other operating expenses & taxes 0 (1600)
(1 row)

=# select regexp_replace('Other operating exp (1600)', '(Other operating exp(enses)?)', 'Other operating expenses & taxes');
             regexp_replace              
-----------------------------------------
 Other operating expenses & taxes (1600)