Is it feasible to escape ' (single quote) with ESCAPE clause, if yes, then how to do it? I understand that we can use '' to escape a single quote in Oracle queries. However, I want to know if it is feasible to escape a single quote in the ESCAPE clause.

set up:

create table test_t ( x varchar2(100));
insert into test_t values ('it''s a string');
commit;


Dummy Query:

select * from test_t
 where x like '%''%' escape '''';
Why do you think it should be escaped? Special characters that should be escaped when using LIKE operator are % and _.

SQL&gt; select * from test_t where x like '%''%';

X
--------------------------------------------------------------------------------
it's a string

SQL&gt; select * from test_t where x like q'[%'%]';

X
--------------------------------------------------------------------------------
it's a string