CODEWITHSUNDEEP
sqloracle-databaseplsqlreplacelistagg
Yash Rafa
Yash Rafa

Reputation: 5

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

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;

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

Upvotes: 0

Views: 1619

Answers (2)

Fact
Fact

Reputation: 2460

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) ||')'

If you want the braces

Upvotes: 1

Arun Palanisamy
Arun Palanisamy

Reputation: 5469

REPLACE.

Are you looking for this?

Select replace('ABCD123,EFGH456,IJKL789',',',q'[',']') From dual;

OUTPUT

ABCD123','EFGH456','IJKL789

Upvotes: 0

Related Questions