0

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 '''';
2

Why do you think it should be escaped? Special characters that should be escaped when using LIKE operator are % and _.

SQL> select * from test_t where x like '%''%';

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

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

X
--------------------------------------------------------------------------------
it's a string
  • I understand that. I am wondering if it can be done. (ESCAPE with a single quote as argument) – Sameer Pradhan Oct 31 '18 at 15:34
  • @SameerPradhan select * from test_t where x like '%''''%' escape ''''; – Balazs Papp Oct 31 '18 at 15:55
  • WOW.. How did you do that!! How 4 single quotes ended up searching string with a single quote. Please explain a bit. – Sameer Pradhan Oct 31 '18 at 18:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.