oracle - replace value in string by excluding case when value is between parentheses

Question

i would like to do an automatic replace with an oracle query in multiple strings available in an oracle table, by changing fields separator ','between first occurrence of 'select' and first occurrence of 'from' without having impact in case is in place a function:

AS IS:

with result as (select column_a, column_b, count(*)  from (select column1, column2, column3 ..
select column1, count(distinct coalesce(column2, cast('zz' as nvarchar2(2)))) as value from ...
select count(*) from (select ....

EXPECTED:

with result as (select column_a || column_b || count(*) from (select column1, column2, column3 ..
select column1 || count(distinct coalesce(column2, cast('zz' as nvarchar2(2)))) as value from ...
select count(*) from (select column1, column2, column3 ..

Those are the srtings i have in the column (AS IS). I expect that after replace_regexp to have that replace mentioned above (EXPECTED). In that column i store sql quries that i have to to run later. — Mathew Linton
– Mathew Linton, Commented Jul 20, 2023 at 5:58

PKey · Accepted Answer · 2023-07-20 06:25:32Z

0

How about not even using replace_regexp, just use instr , substr and replace

with dt as (select 
'with result as (select column_a, column_b, count(*)  from (select column1, column2, column3 ..
select column1, count(distinct coalesce(column2, cast("zz" as nvarchar2(2)))) as value from ...
select count(*) from (select ....' as str from dual ),
 temp as ( 
select instr(str,'select'), instr(str,'from'), replace(substr(str,instr(str,'select')+length('select'),instr(str,'from')-instr(str,'select')-length('select')),',' ,' || ') fixed,
substr(str,0,instr(str,'select')+length('select')) astart ,
substr(str,instr(str,'select')+length('select'),instr(str,'from')-instr(str,'select')-length('select')) middle,
substr(str,instr(str,'from')) theend 
 from dt
 )
 select astart || middle || theend as  original, astart || fixed || theend as res
  from temp

Here is the result, all you'll need later is to write an appropriate update statement.

original 

with result as (select  column_a, column_b, count(*)  from (select column1, column2, column3 ..
select column1, count(distinct coalesce(column2, cast("zz" as nvarchar2(2)))) as value from ...
select count(*) from (select ....

result

with result as (select  column_a ||  column_b ||  count(*)  from (select column1, column2, column3 ..
select column1, count(distinct coalesce(column2, cast("zz" as nvarchar2(2)))) as value from ...
select count(*) from (select ....

answered Jul 20, 2023 at 6:25

PKey

3,8712 gold badges21 silver badges45 bronze badges

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

3 Comments

Mathew Linton Over a year ago

This works only if values are concatenated as you did in the example, but if each query is a different row, in case of count(distinct coalesce(column2, cast("zz" as nvarchar2(2)))) it will also replace separator and is not not correct

PKey Over a year ago

@MathewLinton I see what you mean, I didn't notice the last part, that you require the function calls to remain untouched... well then good luck with that

PKey Over a year ago

@MathewLinton also each line is a different row with different select , I mistook it for a single large query. May be you should make it more clear in the description for others to understand.

Collectives™ on Stack Overflow

oracle - replace value in string by excluding case when value is between parentheses

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related