Simplify String Literals with Dollar-Quoted String Literals

Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

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/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/LanguageElements/Literals/Dollar-quotedStringLiterals.htm

Have fun!

Sign In or Register to comment.