How to escape single quote in Oracle SQL

You may need to update or query data where quote character comes in between string values. To query or update columns, there are two ways to achieve:

  • Alternative Quote Operator ( q'[Text containing Quote e.g. Invoice Ids]’ ) to escape quote symbol in SQL.
update xx_ap_invoices set attribute1 = q'[Test's]'
where invoice_id = 123;
select ATTRIBUTE1 from xx_ap_invoices
where invoice_id = 123;
  • Add one extra quote character
update xx_ap_invoices set attribute1 = 'Test''s'
where invoice_id = 123;