-1

I am trying to implement a simple conversion logic to clean up data on huge oracle table using nested replace function like below ,the rules are simple for now say 

LKP_TABLE
-----------
LTD  ----> LIMITED
COMP ----> COMPANY

SELECT REPLACE (REPLACE ( UPPER ('This is AA LTD_COMP') ,'LTD',
'LIMITED'),'COMP','COMPANY') from dual

--output : THIS IS AN AA LIMITED_COMPANY

But in future this can be a long list and I was wondering if there is any solution other than nested replace function. TRANSLATE function can replace only specific characters only.

Note : I have restrictions in creating a custom PL/SQL functions

Improve this question
1
  • 1
    The problem is that you generally need to apply this sort of logic in an order, otherwise you end up with an even bigger mess. If this is all you have at the moment don't write some complicated thing that may not suit your future needs. If you ever need to write the complicated thing, be prepared to abandon the database for Java/Python etc and spend a lot of time thinking about how. or, just buy some address standardisation software. source: I design and build databases that do exactly this. Commented Feb 15, 2016 at 6:41

1 Answer 1

Reset to default
-1

You can maybe try with splitting the string into words, translating each word and afterwards aggregating the string once again. In the example below, I've assumed that the words will be separated by blankspaces and that the maximum number of words is 7 (pivot table)

with replacements as
 (select 'LTD' String, 'Limited' Repl from dual
   union all
  select 'COMP' String, 'Company' Repl from dual
 )
,MyStrings as
 (select 'This  is AA LTD COMP' Str, 1 strnum from dual
  union all
  select 'This is BB LTD COMP', 2  from dual
  union all
  select 'This is BB COMP', 3 from dual
  )
,pivot as (
             select 1 pnum from dual 
  union all  select 2 from dual 
  union all  select 3 from dual 
  union all  select 4 from dual   
  union all  select 5 from dual   
  union all  select 6 from dual 
  union all  select 7 from dual     
  )
,StrtoRow as
(
SELECT rownum rn
      ,ms.strnum
      ,REGEXP_SUBSTR (Str,'[^ ]+',1,pv.pnum) TXT
  FROM MyStrings ms
      ,pivot pv
where REGEXP_SUBSTR (Str,'[^ ]+',1,pv.pnum) is not null
)
Select Listagg(NVL(Repl,TXT),' ') within group (order by rn) 
from
(
Select sr.TXT, r.Repl, sr.strnum, sr.rn
  from StrtoRow sr
      ,replacements r
 where sr.TXT = r.String(+) 
order by strnum, rn 
) group by strnum
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

This is an interesting answer, I would add it to the duplicate question: stackoverflow.com/questions/83856/…. Please add it there.
would you like to add it there? It is a new approach for the problem.
Hi Florin, Good idea! (^_^)=b I'll do so!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.