Click here to Skip to main content
15,877,637 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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;
Posted
Updated 16-Apr-18 1:36am
Comments
GKP1992 13-Apr-18 7:50am    
Posting the same question twice will not get the result twice as fast. In fact, it will discourage people from helping you. Please close one of the questions.
Member 13777735 13-Apr-18 8:28am    
Shall i get any help for the same.
Member 13777735 16-Apr-18 3:34am    
No one can help me for this. So thanks to every one.
Member 13777735 16-Apr-18 7:37am    
I have completed this by my self.
Member 13777735 13-Apr-18 8:17am    
Apologies for the same. Actually I thought my account is not created properly. Because i have not got any answer from that account still it is 1 day completed. That is why i have posted it again from the different account.

1 solution

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;
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900