使用oracle pl/sql从地址中删除重复的单词 [英] Remove duplicate words from a address using oracle pl/sql

问题描述

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

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

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

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

1. '3 Mayers Court 3 Mayers Court':地址中的单词总数为偶数，并且所有单词/单词组合都是重复的.

1. '3 Mayers Court 3 Mayers Court' : where total no of words in address is even and either all words/combination of words are duplicate.

2. 'Manor House Manor'或'1 Briar Cottages 1 Briar':地址中的单词总数不为奇数，因此有一个中间单词，其中间的所有单词/单词组合都是重复的.

2. 'Manor House Manor'  or '1 Briar Cottages 1 Briar': where total no of words in address is odd and thus there is a middle word across which all words/combination of words on its left and right are duplicate.

我可以通过代码来做到这一点，但是我不知道如何通过PL/SQL删除重复的单词.我被指示通过PL/SQL匿名块或通过函数来​​执行此操作.任何帮助将不胜感激.

I can do this through code, but I've no idea how to remove duplicate words through PL/SQL. I've been instructed to do this through PL/SQL anonymous block or through a function. Any help would be appreciated.

推荐答案

如果仅这些情况出现在您的数据中，则可以在下面使用查询.您可以将此逻辑放入函数中，但查询更快，更简单.

If these are the only cases that may appear in your data you could use query below. You can put this logic into function, but query is faster, simpler.

这里没什么好想的，我只是将字符串分成两半并与源进行比较.对于给定的示例有效，显然，在某些情况下，您需要更多的逻辑.例如，如果字符串中有连续的空格，则必须首先删除它们.

Nothing fancy here, I'm just dividing string into half and comparing with source. Works for given examples, obviously there may be cases where you need more logic. For instance if you have consecutive spaces in string you have to get rid of them at first.

^演示

select address, 
       case when address like sub||'%' 
            then substr(address, 1, length(address) - length(sub)) 
            else address
       end trimmed
  from (select address, trim(substr(address, instr(address, ' ', 1, sn/2 + 1))) sub 
          from (select address, regexp_count(address, ' ') sn from t))

结果:

ADDRESS                        TRIMMED
-----------------------------  -----------------------------
3 Mayers Court 3 Mayers Court  3 Mayers Court
905 Mayers Street              905 Mayers Street
Manor House Manor              Manor House
1 Briar Cottages 1 Briar       1 Briar Cottages

这篇关于使用oracle pl/sql从地址中删除重复的单词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！