I've a doubt regarding replacing / substituting values in PLSQL. I've used listagg to segregate n number of values with each and every value being splitted by comma delimiter. For instance when executing the above query it returns 7digit alpha numeric values. For example ABCD123,EFGH456,IJKL789 After storing the above values in a variable. When trying to replace the comma (,)

How to use Replace/Substitution function in PL SQL

I’ve a doubt regarding replacing / substituting values in PLSQL. I’ve used listagg to segregate n number of values with each and every value being splitted by comma delimiter.

select listagg(column_name,',') with group (order by column_name)
Into new_variable
from table

​x
 
select listagg(column_name,',') with group (order by column_name)Into new_variablefrom table​

For instance when executing the above query it returns 7digit alpha numeric values. For example

ABCD123,EFGH456,IJKL789

After storing the above values in a variable. When trying to replace the comma (,) with this (‘,’) using replace function.

Select replace(new_variable,',','','')
From dual;

 
Select replace(new_variable,',','','')From dual;​

It returns the error invalid number of arguments. Is there anyway other than replace function to replace (,) with (‘,’). Thanking you in advance.

Desired output: ABCD123′,’EFGH456′,’IJKL789

Answer

The escape is either a q' or 3 ' s If you wanted to do it in the listagg:

 select '('|| listagg(col,''',''') WITHIN  GROUP  (order by 1) ||')'

 
 select '('|| listagg(col,''',''') WITHIN  GROUP  (order by 1) ||')'​

If you want the braces

Advertisement

Answer