CODEWITHSUNDEEP
sqloracle-databaseapostrophe
James
James

Reputation: 132

Oracle SQL add apostrophes

Is there a way to deal with apostrophes/multiples quotes in a SQL oracle string? I tried with listagg but only able to add the commas but not the apostrophes/multiples quotes. The goal is to add the users in another SQL Table -> IN Operator

Example: 

select LISTAGG(username, ',') WITHIN GROUP (ORDER BY username) "USERNAME" from user

Current output: james, arthur, peter, gina, lehner

Goal: 'james', 'arthur', 'peter', 'gina', 'lehner'

Any suggestions?

Upvotes: 2

Views: 486

Answers (3)

Marmite Bomber
Marmite Bomber

Reputation: 21075

The simplest solution is to add the apostrophes in the delimiter and add a leading and trailing apostroph

 select ''''||listagg(username,q'[', ']') within group (order by username)||'''' as txt from names

gives

 'ames', 'arthur', 'gina', 'lehner', 'peter'

Upvotes: 2

Mureinik
Mureinik

Reputation: 311428

You can apply any row-function (such as concatination) on the column before applying a window-function to it:

SELECT LISTAGG('''' || username || '''', ',') WITHIN GROUP (ORDER BY username) "USERNAME"
FROM   user

Upvotes: 2

gsalem
gsalem

Reputation: 2028

Try this

select LISTAGG(''''||username||'''', ',') WITHIN GROUP (ORDER BY username) "USERNAME" from user

Upvotes: 2

Related Questions