How to handle a single quote in Oracle SQL

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.

Answer :

You need to escape the ‘ by doubling them :

select * from table where reason = ”’missed transaction”’;

Best Method

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