Problem: I have a column containing certain expressions stored as a text string which include single quotations, such as ‘missed transaction’ (INCLUDING the quotations)
How can I use a where clause with such an occurrence?
select * from table where reason = ”missed transaction”
doesn’t work, and I can’t use the replace function because it also requires single quotations in its syntax. Obscure problem, i know. But thanks for any help.
You need to escape the ‘ by doubling them :
select * from table where reason = ”’missed transaction”’;
The q quote syntax makes this sort of thing easier:
select * from table where reason = q'[‘missed transaction’]’
Everything between the ‘[ and the ]’ is interpreted literally, so no need to double all the quotes, however many there may be.
Reference : http://askanantha.blogspot.com/2007/12/q-quote-operator-introduced-in-oracle.html