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 :

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s