Oracle SQL add apostrophes

Question

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?

score 2 · Answer 1 · answered Dec 27 '19 at 15:53

2

Try this

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

answered Dec 27 '19 at 15:53

gsalem

1,957
1
8
7

Thx you! for the explanation and support! – James Dec 27 '19 at 15:56
Perhaps the easiest way is to NOT build a list at all. Since the goal is to "add the users in another SQL Table -> IN, and that list must be built by a select. You are selecting user names in order to construct this list which will need to be de-constructed in the other query. Why not just incorporate the select that builds the list directly into the SQL -> IN. So Select Table ... where column_name in (Select username from user). – Belayer Dec 29 '19 at 04:30

Mureinik · Answer 2 · 2019-12-27T15:59:06.113

2

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

edited Dec 27 '19 at 15:59

answered Dec 27 '19 at 15:53

Mureinik

297,002
52
306
350

Thx you! for the explanation and support! – James Dec 27 '19 at 15:56

score 2 · Accepted Answer · answered Dec 27 '19 at 16:23

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'

Oracle SQL add apostrophes

3 Answers3