如何使用oracle pl / SQL从输入字符串中删除多次出现 [英] How to remove multiple occurance from a input string using oracle pl/SQL

问题描述

使用oracle pl / sql从地址中删除重复的单词：



将有两种类型的地址，下面是示例



1.'The Old Millers Cottage Street Wood Annex Hall老米勒的预期产量是 - 'Cottage Street Wood Annex Hall The Old Millers'。这里有小屋，所以副本可以从左侧或右侧移除任何一个是正确的。



2.'The Old Millers Cottage Farm Street Wood Annex Hall Old Millers的预期产量是 - 'Cottage Farm Street Wood Annex Hall The Old'。这里有小屋，所以副本可以从左侧或右侧移除任何一个是正确的。



我已被指示通过PL / SQL执行此操作匿名阻止或通过函数。任何帮助将不胜感激。



我尝试过：



创建或替换函数FN_ADD_CLEANUP（ADV_IN在VARCHAR2中）

RETURN VARCHAR2是

NO_SPACES NUMBER;

F_ADD VARCHAR2（255）;

T_ADD VARCHAR2（255）;

ADD_OUT VARCHAR2（255）;



BEGIN



- 初始化参数值

SELECT LTRIM（RTRIM（ADD_IN））INTO F_ADD FROM DUAL;



- 没有设置空间

SELECT REGEXP_COUNT（F_ADD，''）INTO NO_SPACES FROM DUAL;



- TRIMED STRING
SELECT TRIM（SUBSTR（F_ADD，INSTR（F_ADD，''，1，NO_SPACES / 2 + 1）））INTO T_ADD FROM DUAL;



- 结果

ADD_OUT：=当F_ADD喜欢T_ADD时的情况||'％'

那么SUBSTR（F_ADD，1，LENGTH（F_ADD） - LENGTH（T_ADD））

ELSE F_ADD

END;



RETURN ADD_OUT;



END;

Remove duplicate words from a address using oracle pl/sql:

There are two types of addresses will be there, below is the example

1.'The Old Millers Cottage Street Wood Annex Hall The Old Millers' expected output is - 'Cottage Street Wood Annex Hall The Old Millers'. Here cottage is present so the duplicate can remove from either left side or else right side any one is correct.

2.'The Old Millers Cottage Farm Street Wood Annex Hall The Old Millers' expected output is - 'Cottage Farm Street Wood Annex Hall The Old'. Here cottage is present so the duplicate can remove from either left side or else right side any one is correct.

I've been instructed to do this through PL/SQL anonymous block or through a function. Any help would be appreciated.

What I have tried:

CREATE OR REPLACE FUNCTION FN_ADD_CLEANUP(ADD_IN IN VARCHAR2)
RETURN VARCHAR2 IS
NO_SPACES NUMBER;
F_ADD VARCHAR2(255);
T_ADD VARCHAR2(255);
ADD_OUT VARCHAR2(255);

BEGIN

-- INITIALIZING THE PARAMETER VALUE
SELECT LTRIM(RTRIM(ADD_IN)) INTO F_ADD FROM DUAL;

-- SET NO OF SPACES
SELECT REGEXP_COUNT(F_ADD, ' ') INTO NO_SPACES FROM DUAL;

-- TRIMED STRING
SELECT TRIM(SUBSTR(F_ADD, INSTR(F_ADD, ' ', 1, NO_SPACES/2 + 1))) INTO T_ADD FROM DUAL;

-- RESULT
ADD_OUT := CASE WHEN F_ADD LIKE T_ADD||'%'
THEN SUBSTR(F_ADD, 1, LENGTH(F_ADD) - LENGTH(T_ADD))
ELSE F_ADD
END;

RETURN ADD_OUT;

END;

推荐答案

with src as (
Select 'A202 A202'                                                        as str from dual union all
Select 'Flat A202 Flat'                                                   as str from dual union all
Select 'The Old The Old'                                                  as str from dual union all
Select 'The Old Millers The Old'                                          as str from dual union all
Select 'The Old Millers The Old Millers'                                  as str from dual union all
Select 'The Old Cottage Millers The Old'                                  as str from dual union all
Select 'The Old Millers Cottage The Old Millers'                          as str from dual union all
Select 'The Old Millers Cottage Annex The Old'                            as str from dual union all
Select 'The Old Millers Cottage The Old Millers Cottage'                  as str from dual union all
Select 'The Old Millers Cottage Annex The Old Millers'                    as str from dual union all
Select 'The Old Cottage Wood Annex Hall The Old'                          as str from dual union all
Select 'The Old Millers Cottage Annex The Old Millers Cottage'            as str from dual union all
Select 'The Old Millers Cottage Wood Annex The Old Millers'               as str from dual union all
Select 'The Old Cottage Street Wood Annex Hall The Old'                   as str from dual union all
Select 'The Old Millers Cottage Annex The Old Millers Cottage Annex'      as str from dual union all
Select 'The Old Millers Cottage Wood Annex The Old Millers Cottage'       as str from dual union all
Select 'The Old Millers Cottage Wood Annex Hall The Old Millers'          as str from dual union all
Select 'The Old Millers Cottage Street Wood Annex Hall The Old'           as str from dual union all
Select 'The Old Millers Cottage Annex Hall The Old Millers Cottage Annex' as str from dual union all
Select 'The Old Millers Cottage Wood Annex Hall The Old Millers Cottage'  as str from dual union all
Select 'The Old Millers Cottage Street Wood Annex Hall The Old Millers'   as str from dual union all
Select 'The Old Millers Cottage Farm Street Wood Annex Hall The Old'      as str from dual
  )
  select listagg(str,' ') within group(order by r) str
  from
  (  select min(r) r, rn, str  from
 (
 SELECT rownum r, rn,
        REGEXP_SUBSTR (str, '[^ ]+',1,n) AS str
    FROM (select rownum rn, str from src) src,
(    SELECT LEVEL n
                 FROM DUAL,
                      (SELECT MAX (REGEXP_COUNT (str, ' ')) + 1 mcomma FROM src)
           CONNECT BY LEVEL <= mcomma) ctr
    WHERE ctr.n <= 1 + REGEXP_COUNT (str, ' ')
 ) group by rn, str) group by rn;

这篇关于如何使用oracle pl / SQL从输入字符串中删除多次出现的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！