How to select a column to appear with two single quote in the field-CodePudding

Here is my postgresql query

select 'insert into employee(ID_NUMBER,NAME,OFFICE) values ('''||ID_NUMBER||''','''||NAME||''','''||replace(DESIGNATION,'&','and')||''','''||replace(DEPT_NAME,'&','and')||''')' as col 
from icare_employee_view 
where id_number='201403241' 
order by name;

output

insert into employee(ID_NUMBER,NAME,OFFICE) values ('201403241','ABINUMAN, JOSEPHINE CALLO','Assistant AGrS Principal for Curriculum and Instruction','AGrS Principal's Office')

but I need 'AGrS Principal's Office' to be 'AGrS Principal''s Office'

but I need 'AGrS Principal's Office' to be 'AGrS Principal''s Office'

any suggestions or sol'n is highly appreciated on how to fix my PostgreSQL query

CodePudding user response：

Hi check this from pgDocs:

quote_literal ( text ) → text

Returns the given string suitably quoted to be used as a string literal in an SQL statement string. Embedded single-quotes and backslashes are properly doubled. Note that quote_literal returns null on null input; if the argument might be null, quote_nullable is often more suitable. See also Example 43.1.

quote_literal(E'O'Reilly') → 'O''Reilly'