Search code examples
sqloracle-databaseapostrophe

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?

Solution

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