How to use replace command for multiple characters in PL/SQL

Question

I'm writing a procedure that have to replace a set of special characters with another set of them that are accepted to an application system. How can I re-write better the following statement that I'm using in the procedure?

select     replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace('%BICI* "(MOTO), |X PLAY? 4G: RED&WHITE& \/<DIRETTA>','(','-'),'%','perc'),'?','.'),'|','-'),':',';'),',','.'),'<','-'),'>','-'),'&','and'),'\','-'),'/','-'),'"','-'),')','-'),'*','-')
from dual;

I can't use a recursive procedure. Any suggestions?

Thanks! Ilaria

Maybe this Answer can fit your needs: stackoverflow.com/a/2947787/6773490 — tchibu
– tchibu, Commented Aug 30, 2016 at 14:09
How are the mappings determined - are they in a table? Or do you need to hard-code them? (Definitely hoping it's the former and not the latter!) Then, what do you mean by "can't use a recursive procedure" - do you mean you can't use procedures? Can you use recursive queries? (That is a straight SQL concept, it has nothing to do with procedures.) Finally, after you make all the substitutions, is it possible that the new string again has possible "substitution substrings" and if so, do you need to call this again upon itself until all possible substitutions are made? — user5683823
– user5683823, Commented Aug 30, 2016 at 15:49
The question of "recursive queries" depends on your Oracle version, is it 11.2 or above? If it is, and especially if you need the solution to continue to substitute recursively until no further substitution is possible, you may want to take a look at this: stackoverflow.com/questions/39224899/… (Still working on cleaning it up some more!) — user5683823
– user5683823, Commented Aug 30, 2016 at 15:51

Gary_W · Accepted Answer · 2016-08-30 16:02:49Z

This is an interesting one. Here's a working example based on Florin's example:

with trans_tbl(id, symbol, txt) as (
  select 1, '(', '-'    from dual union
  select 2, '%', 'perc' from dual union
  select 3, '?', '.'    from dual union
  select 4, '|', '-'    from dual union
  select 5, ':', ';'    from dual union
  select 6, ',', '.'    from dual union
  select 7, '<', '-'    from dual union    
  select 8, '>', '-'    from dual union   
  select 9, '&', 'and'  from dual union   
  select 10, '\', '-'   from dual union    
  select 11, '/', '-'   from dual union
  select 12, '"', '-'   from dual union
  select 13, ')', '-'   from dual union
  select 14, '*', '-'   from dual 
),
data_tbl(str) as (
  select '%BICI* "(MOTO), |X PLAY? 4G: RED&WHITE& \/<DIRETTA>' from dual
),
working_tbl(str, id) as (
  SELECT str, 0 id 
  FROM data_tbl 
     UNION ALL
  SELECT replace(working_tbl.str,symbol,txt), trans_tbl.id
  FROM working_tbl 
  JOIN trans_tbl 
    ON working_tbl.id = trans_tbl.id - 1
)
--select str, id from working_tbl;
SELECT str 
from working_tbl 
where id = (select max(id) 
            from trans_tbl);

STR
----------------------------------------------------------
percBICI- --MOTO-. -X PLAY. 4G; REDandWHITEand ---DIRETTA-

Collectives™ on Stack Overflow

How to use replace command for multiple characters in PL/SQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related