All database systems make provisions for embedding literal values in SQL statements. Text and numeric literals are usually quite simple, but there are some nuances of which you should be aware. Date and time literals tend to be more complex.

Text Literals

The ISO SQL standard for text literals is to enclose them within single quotes:

'This is a text literal'

Use two adjacent single quotes when you need to embed a single quote within a string:

'Isn''t SQL fun?'

SQL will treat the two adjacent single quotes as a single quote within the literal:

Isn't SQL fun?

Oracle Database 10g allows you to specify alternative quoting delimiters, which are always two characters and always include leading and trailing single quotes. For example, to use '[ and ]' as delimiters, specify:

Q'[This isn't as bad as it looks]'
q'[This isn't as bad as it looks]'

The (, [, and { characters are special cases in that their corresponding closing delimiters must be ), ], and }, respectively. Otherwise, use the same character to close the string that you use to open it:

Q'|This string is delimited by vertical bars|'

You can’t use space, tab, or return characters to delimit a string in this manner.

PostgreSQL allows you to specify alternative quoting delimiters using a dollar-sign syntax, producing a dollar-quoted string constant. For example:

$tag$This is a dollar-quoted string constant$tag$

Replace tag with any desired sequence of characters. Your quoting delimiter is then $tag$. If you like, you can even use $$ without ...

Get SQL Pocket Guide, 2nd Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.