0

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 ..
Mathew Linton
  • 33
  • 1
  • 2
  • 8
  • where is all your table DDL, sample data, expected output? – OldProgrammer Jul 19 '23 at 21:54
  • 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 Jul 20 '23 at 05:58

1 Answers1

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 ....
PKey
  • 3,715
  • 1
  • 14
  • 39
  • 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 – Mathew Linton Jul 20 '23 at 07:10
  • @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 Jul 20 '23 at 07:31
  • @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. – PKey Jul 20 '23 at 07:35