O'Reilly logo

SQL Pocket Guide, 2nd Edition by Jonathan Gennick

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Literals

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required