How to remove multiple occurance from a input string using oracle pl/SQL

Question

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 13-Apr-18 0:19am

Member 13777735

Updated 16-Apr-18 1:36am

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Member 13777735 · Answer 1 · 2018-04-16T01:37:00

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;