Dollar-Quoted String Constants

Moderator: NorbertKrupa

Post Reply
User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1806
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Dollar-Quoted String Constants

Post by JimKnicely » Thu Mar 07, 2013 3:04 pm

Hi,

It’s not an uncommon situation where you’ll be attempting to cope with data that contains many single quotes. It’s not a simple task to read and write data of this type because the single quote in Vertica is used to indicate the start and end of a string literal. Vertica can’t tell the difference between a single quote inside of the string and those that are supposed to enclose the string.

For example, if I try the following simple SELECT of a string value that contains single quotes, I’ll get an error:

Code: Select all

dbadmin=> SELECT 'Jim's dog ate Bill's cat's pet mouse's cheese!';
ERROR 4856:  Syntax error at or near "dog" at character 15
LINE 1: SELECT 'Jim's dog ate Bill's cat's pet mouse's cheese!';
                      ^
One solution would be to double every instance of the single quote character:

Code: Select all

dbadmin=> SELECT 'Jim''s dog ate Bill''s cat''s pet mouse''s cheese!' AS "Single Quote Doubled";
               Single Quote Doubled
------------------------------------------------
Jim's dog ate Bill's cat's pet mouse's cheese!
(1 row)
But that’s a bit tedious. This’s why PostgreSQL provides another way, called “Dollar Quoting”. Since Vertica finds its roots in PostgreSQL we can utilize many of its features!

Code: Select all

dbadmin=> SELECT $$Jim's dog ate Bill's cat's pet mouse's cheese!$$ AS "dollar-quoted";
                  dollar-quoted
------------------------------------------------
Jim's dog ate Bill's cat's pet mouse's cheese!
(1 row)
Another example displays how we can use “dollar quoting” to easily insert data containing many single quotes into a table:

Code: Select all

dbadmin=> CREATE TABLE t (c VARCHAR(100));
CREATE TABLE

dbadmin=> INSERT INTO t VALUES ($$Jim's dog ate Bill's cat's pet mouse's cheese!$$);
OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT c FROM t;
                        c
------------------------------------------------
Jim's dog ate Bill's cat's pet mouse's cheese!
(1 row)
Have fun!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica Tips, Lessons and Examples”