Finding a record with apostrophe using LIKE

Question

I need to find a below record

O'GRAD

I wanted to use LIKE

LIKE 'O'GRAD'

But i come across a problem with apostrophe. What would be the best way around it?

Answer 1

Double up the apostrophe

LIKE 'O''GRAD'

or use the Q syntax

LIKE q'{O'GRAD}'

Answer 2

if your Oracle DB version is 10g or upper you may use :

select * 
  from mytable t
 where t.col1 like '%'||q'$O'GRAD$'||'%';
/

or classically add an extra quote to existing quote

select * 
  from mytable t
 where like '%'||'O''GRAD'||'%';
/

to overcome the single quotation mark problem.