如何使用oracle PL/SQL从输入字符串中删除多个单词出现

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

有两种类型的地址会在那里，下面是示例

1.“3 Mayers Court”: 地址中的单词总数是均匀的，并且所有单词/单词组合都是重复的。

2.“庄园庄园” 或 “1 Briar Cottages 1 Briar”: 地址中的单词总数为奇数，因此有一个中间单词，其左右两侧的所有单词/单词组合都是重复的。

3.“旧磨坊小屋木附楼旧磨坊小屋” 的预期产量为-“旧磨坊小屋木附楼”。这里存在小屋，因此副本可以从左侧或右侧删除，任何一个都是正确的。

4.'旧磨坊农舍农场街木材附楼旧' 预期产量为 '旧磨坊农舍农场街木材附楼'。这里存在小屋，因此副本可以从左侧或右侧删除，任何一个都是正确的。

像这样，字符串之间的空间可以是1到10，并且会有更多的示例，例如，

A202 A202
Flat A202 Flat
The Old The Old
The Old Millers The Old
The Old Millers The Old Millers
The Old Cottage Millers The Old
The Old Millers Cottage The Old Millers
The Old Millers Cottage Annex The Old
The Old Millers Cottage The Old Millers Cottage
The Old Millers Cottage Annex The Old Millers
The Old Cottage Wood Annex Hall The Old
The Old Millers Cottage Annex The Old Millers Cottage
The Old Millers Cottage Wood Annex The Old Millers
The Old Cottage Street Wood Annex Hall The Old
The Old Millers Cottage Annex The Old Millers Cottage Annex
The Old Millers Cottage Wood Annex The Old Millers Cottage
The Old Millers Cottage Wood Annex Hall The Old Millers
The Old Millers Cottage Street Wood Annex Hall The Old
The Old Millers Cottage Annex Hall The Old Millers Cottage Annex
The Old Millers Cottage Wood Annex Hall The Old Millers Cottage
The Old Millers Cottage Street Wood Annex Hall The Old Millers
The Old Millers Cottage Farm Street Wood Annex Hall The Old

因此，我必须删除上述字符串中所有不同类型的重复单词。
任何帮助将不胜感激。

我已经尝试了以下代码，但无法满足所有测试用例。下面是我的代码，

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; 

一点正则表达式可以帮助你:

(.*)(.*)\1

会发现:

1任意系列字符
2随后是其他任何角色的系列
3后跟在步骤1匹配的字符串:

with rws as (
  select '3 Mayers Court 3 Mayers Court' str from dual union all
  select 'Manor House Manor' str from dual union all 
  select '1 Briar Cottages 1 Briar' str from dual union all
  select 'The Old Millers Cottage Wood Annex Hall The Old Millers Cottage' str from dual union all
  select 'The Old Millers Cottage Farm Street Wood Annex Hall The Old' str from dual
)
  select str, regexp_replace(str, '(.*)(.*)\1', '\1\2') from rws;

STR                                                               REGEXP_REPLACE(STR,'(.*)(.*)\1','\1\2')                
3 Mayers Court 3 Mayers Court                                     3 Mayers Court                                         
Manor House Manor                                                 Manor House                                            
1 Briar Cottages 1 Briar                                          1 Briar Cottages                                       
The Old Millers Cottage Wood Annex Hall The Old Millers Cottage   The Old Millers Cottage Wood Annex Hall                
The Old Millers Cottage Farm Street Wood Annex Hall The Old       The Old Millers Cottage Farm Street Wood Annex Hall    

但是保重。这只会删除第一个重复的:

with rws as (
  select '3 Mayers Court 3 Mayers Court 3 Mayers Court' str from dual 
)
  select regexp_replace(str, '(.*)(.*)\1', '\1\2') from rws;

REGEXP_REPLACE(STR,'(.*)(.*)\1','\1\2')   
3 Mayers Court 3 Mayers Court             

并且可能无法对某些数据起作用:

with rws as (
  select 'Big House 3 Manor House 3 Manor House' str from dual 
)
  select regexp_replace(str, '(.*)(.*)\1', '\1\2') from rws;

REGEXP_REPLACE(STR,'(.*)(.*)\1','\1\2')   
Big House 3 Manor House 3 Manor House     

确保你测试thoroughly在您的目标数据集上。