如何“全局"替换多个单词?在 Oracle 中使用 regexp_replace 吗? [英] How can I replace multiple words "globally" using regexp_replace in Oracle?

问题描述

我需要用字符串中的空字符替换多个单词，例如 (dog|cat|bird)，其中一个单词可能连续出现多次.实际代码是从名称中删除称呼和后缀.不幸的是，我得到的垃圾数据有时包含SNERD JR JR".

I need to replace multiple words such as (dog|cat|bird) with nothing in a string where there may be multiple consecutive occurrences of a word. The actual code is to remove salutations and suffixes from a name. Unfortunately the garbage data I get sometimes contains "SNERD JR JR."

我能够创建一个正则表达式模式来实现我的目标，但仅限于第一次出现.我实施了一个愚蠢的黑客来摆脱第二次发生，但我相信必须有更好的方法.我就是想不通.

I was able to create a regular expression pattern that accomplishes my goal but only for the first occurrence. I implemented a stupid hack to get rid of the second occurrence, but I believe there has to be a better way. I just can't figure it out.

这是我的黑客"代码；

  FUNCTION REMOVE_SALUTATIONS(IN_STRING VARCHAR2) RETURN VARCHAR2 DETERMINISTIC
  AS
    REGEX_SALUTATIONS VARCHAR2(4000) := '(^|\s)(MR|MS|MISS|MRS|DR|MD|M D|SR|SIR|PHD|P H D|II|III|IV|JR)(\.?)(\s|$)';
  BEGIN
    RETURN TRIM(REGEXP_REPLACE(REGEXP_REPLACE(IN_STRING,REGEX_SALUTATIONS,' '),REGEX_SALUTATIONS,''));
  END REMOVE_SALUTATIONS;

我真的很自豪能够做到这一点，因为正则表达式对我来说不是很规律.感谢所有帮助.

I was actually proud that I was able to get this far, as regular expression are not very regular to me. All help is appreciated.

根据我的理解，regexp_replace 的默认值是进行全局替换.但在外部机会我的数据库配置不同我确实尝试过；

The default for regexp_replace based on my understanding is to do a global replace. But on the outside chance my DB is configured different I did try;

select REGEXP_REPLACE('SNERD JR JR','(^|\s)(MR|MS|MISS|MRS|DR|MD|M D|SR|SIR|PHD|P H D|II|III|IV|JR)(\.?)(\s|$)',' ',1,0) from dual;

结果是;

SNERD JR

推荐答案

使用REGEXP_REPLACE函数的occurrence参数.文档说:

Use occurrence parameter of REGEXP_REPLACE function. The docs says:

occurrence 是一个非负整数，表示替换操作的发生:

occurrence is a nonnegative integer indicating the occurrence of the replace operation:

• 如果您指定 0，则 Oracle 将替换所有匹配项.
• 如果指定正整数 n，则 Oracle 替换第 n 个出现

https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions137.htm#SQLRF06302

它应该是这样的:

...
REGEXP_REPLACE(IN_STRING,REGEX_SALUTATIONS,' ', 1,0 )
...

这篇关于如何“全局"替换多个单词?在 Oracle 中使用 regexp_replace 吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！