Using apostrophe in like clause in oracle sql query for wild search

Question

I am trying to search for name that has apostrophe in it, e.g O'Brian and trying to use the below query.

select * from <Table Name> where first_name like '%'%';

However, I am getting error for invalid identifier

Answer 1

Because Oracle interprets the second ' as the end of your string, so the remaining part %' is beyond the string, and it's parsed as SQL, and obviously it's invalid syntax of SQL.

To prevent this you should escape the middle ' symbol, in Oracle it's done by typing two consequent apostrophe, so you do '%''%'

Answer 2

You can use Alternative Quoting Mechanism as q'[]'

In your case query should be like this:

select * from <Table Name> where first_name like q'[%'%]';