sql - Oracle PL/SQL中的字符串删除

我需要从输入字符串中删除某些关键字，然后返回新字符串。关键字存储在另一个表中，例如MR，MRS，DR，PVT，PRIVATE，CO，COMPANY，LTD，LIMITED等。它们是两种领先的关键字-MR，MRS，DR和TRAILING-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来完成。我已经编写了以下代码，但是如果在开头或结尾出现多个关键字，它将删除所有关键字。我在关键字的游标中循环时的原因是，尽管不是结尾且循环的关键字已经被迭代，但是我们不能重用该关键字进行替换。请注意，结尾处或开头都不能有n个关键字:

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

最佳答案

我认为可以通过单个查询来完成(如果出于某种原因可以将其包装在plsql函数中):

Here is a sqlfiddle demo

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'中的关键字外部连接起来，这将为输入中的每个单词提供前导，尾随或null(如果不存在)

到目前为止，我们已经有了(对于"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

现在到了棘手的部分(也许有更好的方法)-我们需要以某种方式知道要删除哪个单词，应该是所有LEADING直到“更改”为止，这意味着直到我们找到空值或TRAILING(自上而下)，并且所有的跟踪，直到“更改”为止，表示空值或LEADING(自下而上)。因此，我使用了一个已知的Technic来累计总和，只要我们仍为零，就需要删除该行(一旦获得“更改”，我们将具有一些值)。

就是这样，我们现在要做的就是将行重新收集到一个新的字符串中，因为11gr2我们可以为此使用LISTAGG

关于sql - Oracle PL/SQL中的字符串删除，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/25504587/

mrs

sql - Oracle PL/SQL中的字符串删除