4

I have a column containing certain expressions stored as a text string which include single quotatons, such as 'missed transaction' (INCLUDING the quotations)

How can I use a where clause with such an occurance?

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.

Jean-François Savard
  • 20,626
  • 7
  • 49
  • 76
Kevin
  • 207
  • 2
  • 4
  • 12
  • I don't know Oracle, but most SQL systems use 'doubling' as escape sequences, e.g. to escape `'` use `''`. Did you try `'''missed transaction'''`? – p.s.w.g Mar 06 '15 at 19:41
  • 1
    If your query is coming from an application like .net, java, coldfusion, etc, use query parameters. Escaping quotes is one of their benefits. – Dan Bracuk Mar 06 '15 at 19:57

2 Answers2

17

You need to escape the ' by doubling them :

select * from table where reason = '''missed transaction''';
Jean-François Savard
  • 20,626
  • 7
  • 49
  • 76
11

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.

Jeffrey Kemp
  • 59,135
  • 14
  • 106
  • 158
  • This is perfect for my surname search e.g. surnames like O'Reilly: SELECT * WHERE SURNAME LIKE q'[" + surname + "%]' "; – Scott Feb 27 '18 at 11:55