Oracle PL/SQL中的字符串删除

Question

我需要从输入字符串中移除特定的关键词并返回新的字符串。这些关键词存储在另一个表中，如MR、MRS、DR、PVT、PRIVATE、CO、COMPANY、LTD、LIMITED等。它们有两种类型：前导型-MR、MRS、DR；尾随型-PVT、PRIVATE、CO、COMPANY、LTD、LIMITED等。因此，如果关键词是前导型，则必须从开头删除；如果是尾随型，则必须从结尾删除。例如-MR Jones MRS COMPANY应返回JONES MRS，MR MRS Jones PVT COMPANY应返回JONES（因为在第一次迭代中将修剪MR和PVT，然后单词将变成MRS JONES PVT，在第二次迭代中，它将变成JONES。类似地，MR MRS Doe PVT COMPANY LTD最终将返回DOE。 我必须通过PL/SQL实现。我编写了以下代码，但如果有多个关键词位于开头或结尾，它会删除所有关键词。原因是当我循环遍历关键字的光标时，如果已经迭代过不在末尾的关键字，则无法重用该关键字进行替换。请注意，开头或结尾可能有任意数量的关键词:

CREATE OR REPLACE FUNCTION replace_keyword (p_in_name IN VARCHAR2)
   RETURN VARCHAR2
IS
   l_name   VARCHAR2 (4000);

   CURSOR c
   IS
      SELECT *
        FROM RSRV_KEY_LKUPS
       WHERE ACTIVE = 'Y';
BEGIN
    l_name := TRIM (p_in_name); 

   --Now inside the function we’ll loop through this cursor something like below and replace the value in the input name:

   FOR rec IN c
   LOOP
      IF     UPPER (rec.POSITION) = 'LEADING'
         AND INSTR (UPPER (l_name), UPPER (rec.KEY_WORD || ' '), 1) > 0
      THEN                                        --Rule 3:remove leading name
         DBMS_OUTPUT.PUT_LINE ('Value >>' || rec.KEY_WORD);
         l_name := LTRIM (UPPER (l_name), rec.KEY_WORD || ' ');

      ELSIF     UPPER (rec.POSITION) = 'TRAILING'
            AND INSTR (UPPER (l_name), UPPER (' ' || rec.KEY_WORD), -1) > 0
      THEN                                       --Rule 4:remove trailing name
         DBMS_OUTPUT.PUT_LINE ('Value >>' || rec.KEY_WORD);
         l_name := RTRIM (UPPER (l_name), ' ' || rec.KEY_WORD);      
      END IF;

      l_name := l_name;
   END LOOP;

   l_name := REGEXP_REPLACE (l_name, '[[:space:]]{2,}', ' '); --Remove multiple spaces in a word and replace with single blank space
   l_name := TRIM (l_name); --Remove the leading and trailing blank spaces
   RETURN l_name;
EXCEPTION
   WHEN OTHERS
   THEN
      raise_application_error (
         -20001,
         'An error was encountered - ' || SQLCODE || ' -ERROR- ' || SQLERRM);
END;
/

非常感谢您提前的帮助。 编辑 样例输入1

MR MRS Jones PVT COMPANY 

输出

JONES

示例输入2

MR MRS Doe PVT COMPANY LTD 

输出

DOE

- Sid

---

如果您展示样本数据（通过编辑您的问题），并且说明您想要从中返回什么，那将会很有帮助。 - Gordon Linoff

@Gordon - 样例输入输出已添加到末尾，尽管它已经在问题中了。 - Sid

@yamny 我该如何在我的情况下使用正则表达式？ - Sid

问题不在于替换，而是如何在我已经添加的循环之上使用另一个循环，以检查是否已删除所有关键字。 - Sid

@Sid 好的，现在我完全理解了你的问题，我认为可以使用 regexp_replace，但我不知道它是否高效。 - yamny

Answer 1

如果您想确保主要关键字在开头被找到，您应该只在INSTR返回1时删除它： 替换

IF UPPER (rec.POSITION) = 'LEADING'
   AND INSTR (UPPER (l_name), UPPER (rec.KEY_WORD || ' '), 1) > 0

使用

IF UPPER (rec.POSITION) = 'LEADING'
   AND INSTR (UPPER (l_name), UPPER (rec.KEY_WORD || ' '), 1) = 1

和替换

  ELSIF     UPPER (rec.POSITION) = 'TRAILING'
        AND INSTR (UPPER (l_name), UPPER (' ' || rec.KEY_WORD), -1) > 0

by

  ELSIF UPPER (rec.POSITION) = 'TRAILING'
        AND INSTR (UPPER (l_name), UPPER (' ' || rec.KEY_WORD), -1) = (LENGTH(l_name)-LENGTH(rec.key_word) +1)

对于多个关键字的问题，您需要在for循环周围进行循环：

keyword_found BOOLEAN;
LOOP
  keyword_found = false;
  FOR rec IN c
       -- when you find a keyword
       keyword_found := true;
  END LOOP;
  EXIT WHEN NOT(keyword_found);
END LOOP;

- Conffusion

---

你是否将 keyword_found = true 放在了“IF THEN”部分内？我也编辑了我的代码。 - Conffusion

是的，我已经添加了它们...但请忽略它。 - Sid

忘了冒号：关键字 := true; - Conffusion

Answer 2

我认为可以用一条查询语句来完成（如果你坚持要求的话，可以将其封装在PL/SQL函数中）：

这里是一个sqlfiddle演示

with inpt as (select 'MR Jones MRS COMPANY' text from dual)
select listagg(t1.word, ' ') within group (order by ord) new_text 
from (
select w.*, words.*, 
sum(case when nvl(POSITION, 'TRAILING') = 'TRAILING'  then 1 else 0 end) over(order by ord rows between unbounded preceding and current row) l,
sum(case when nvl(POSITION, 'LEADING') = 'LEADING' then 1 else 0 end) over(order by ord desc rows between unbounded preceding and current row) t
from 
(select regexp_substr(inpt.text, '[^ ]+',1,level) word , level ord 
from inpt 
connect by level <= regexp_count(inpt.text, ' ') + 1) words left outer join RSRV_KEY_LKUPS w on w.KEY_WORD = words.word
 ) t1
where t1.t > 0 and t1.l > 0

编辑：解释：

'with'子句只是将输入字符串作为列（并不是必需的）。

内部选择被别名为“words”，这是一种将单词拆分为行的已知技术（请注意，我保留了ord列的顺序）。

现在，我们可以将输入字符串的单词与表“RSRV_KEY_LKUPS”中的关键字左外连接，这将为我们提供每个输入单词是否应为前导或尾随或空（如果不存在）。

因此，到目前为止，我们有（对于输入"MR Jones MRS COMPANY"）：

KEY_WORD    POSITION    WORD    ORD 
----------------------------------- 
MR          LEADING     MR      1  
(null)      (null)      Jones   2  
MRS         LEADING     MRS     3  
COMPANY     TRAILING    COMPANY 4 

现在进入棘手的部分（也许有更好的方法） - 我们需要知道要删除哪个单词，它应该是所有LEADINGs直到“a change”，这意味着直到我们找到一个null或TRAILING（自上而下），以及所有TRAILINGs直到“a change”，这意味着null或LEADING（自下而上）。因此，我使用了一种已知的累积和技术，只要我们还是零，就需要删除该行（一旦我们得到“a change”，我们将有一些值）。 就这样，我们现在需要做的就是重新收集行到一个新字符串中，自从11gr2以来，我们可以使用LISTAGG来实现这一点。

- A.B.Cade