Simplify String Literals with Dollar-Quoted String Literals: Quick Tip

Posted November 6, 2018 by Jim Knicely, Vertica Principal Solution Architect

High angle view of Beijing Guomao.
The standard syntax for specifying string literals can be difficult to understand. To allow more readable queries in such situations, Vertica SQL provides dollar quoting. Dollar quoting is not part of the SQL standard, but it is often a more convenient way to write complicated string literals than the standard-compliant single quote syntax.

Example: dbadmin=> SELECT 'You''ll have to use '''' ''every'' time you want to display a '' in a ''Single-Quoted'' string literal!' "Confusing Syntax"; Confusing Syntax --------------------------------------------------------------------------------- You'll have to use '' 'every' time you want to display a ' in a 'Single-Quoted' string literal! (1 row) dbadmin=> SELECT $$You don't have to use '' 'every' time you want to display a ' in a 'Dollar-Quoted' string literal!$$ "Not Confusing Syntax"; Not Confusing Syntax ------------------------------------------------------------------------------------ You don't have to use '' 'every' time you want to display a ' in a 'Dollar-Quoted' string literal! (1 row) Helpful Link:

https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/LanguageElements/Literals/Dollar-quotedStringLiterals.htm

Have fun!