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

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s