When you are handling string you sometime have single quote in your string. I was looking on how to handle that in Oracle and it’s not pretty but you can cover that case with the chr(39) function, 39 being the code for the single quote.
So it you are looking the “it’s” you have to look for “it‘||CHR(39)||’s”
I’ve read I’ve could have use 2 quote or the $ in latter version. In that case:
SELECT 'it''s' name FROM DUAL;
Or use the new (10g+) quoting method
SELECT q'$it's$' NAME FROM DUAL;
Note I’ve used the chr(39) and worked fine for me I didn’t try the other one, so good luck with that.