Oracle: Replace multiple string occurrences

Question

Just a quick check, is it possible to replace all the occurrences with a single select statement. If not, we plan to write a function to do this replace.

Sample string

select '#col1:val1@, #col2:val2@, #col3:val3@, #col4:val4@, #col5:val5@' str from dual

Expected output

str
---
col1 val1, col2 val2, col3 val3, col4 val4, col5 val5

Code so far

with test_table as 
(Select '#col1:val1@, #col2:val2@, #col3:val3@, #col4:val4@, #col5:val5@' str from dual)

   select level "occurrence", REGEXP_substr(str, '#(.*?):', 1, level, 'in', 1) "column", REGEXP_substr(str, ':(.*?)@', 1, level, 'in', 1) "value"
   from test_table
   CONNECT BY REGEXP_COUNT(str, '#.+?:.+?@') >= level

Additional Details

String pattern is fixed and known beforehand.
Occurrence of the given pattern is dynamic, may have any number of [column:value] pairs - they are actually [table column:alias] set.
I have trimmed the sample string for simplicity, however it contains additional details (table joins, where clause etc.), so we would need to replace all the occurrences. It’s actually a select statement.

MT0 · Accepted Answer · 2016-06-14 10:09:09Z

3

Query:

SELECT REGEXP_REPLACE(
         '#col1:val1@, #col2:val2@, #col3:val3@, #col4:val4@, #col5:val5@',
         '#(.*?):(.*?)@',
         '\1 \2'
       ) AS str
FROM   DUAL

Output:

STR
-----------------------------------------------------
col1 val1, col2 val2, col3 val3, col4 val4, col5 val5

answered Jun 14, 2016 at 10:09

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Florin Ghita · Accepted Answer · 2016-06-14 10:35:10Z

0

My try:

with test_table as 
(Select '#col1:val1@, #col2:val2@, #col3:val3@,     #col4:val4@, #col5:val5@' str 
 from dual)

select regexp_replace(regexp_replace(str, '[#@]', ''), '[ :]+', ' ') rez 
from test_table
;

The inner regexp_replace replaces the # and @ with nothing(empty string) and the outer regexp replaces the : and spaces with one space.

edited Jun 14, 2016 at 10:35

answered Jun 14, 2016 at 10:17

Florin Ghita

17.7k6 gold badges60 silver badges78 bronze badges

Collectives™ on Stack Overflow

Oracle: Replace multiple string occurrences

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related