Extended String Literals

Syntax

E'characters'

Parameters

characters

Arbitrary sequence of characters bounded by single quotes (')

You can use C-style backslash sequence in extended string literals, which are an extension to the SQL standard. You specify an extended string literal by writing the letter E as a prefix (before the opening single quote); for example: 

E'extended character string\n'

Within an extended string, the backslash character (\) starts a C-style backslash sequence, in which the combination of backslash and following character or numbers represent a special byte value, as shown in the following list. Any other character following a backslash is taken literally; for example, to include a backslash character, write two backslashes (\\).

When an extended string literal is concatenated across lines, write only E before the first opening quote:

=> SELECT E'first part o'
    'f a long line';
         ?column?
---------------------------
 first part of a long line
(1 row)

Two adjacent single quotes are used as one single quote:

=> SELECT 'Aren''t string literals fun?';
          ?column?
-----------------------------
 Aren't string literals fun?
(1 row)

Standard Conforming Strings and Escape Characters

When interpreting commands, such as those entered in vsql or in queries passed via JDBC or ODBC, Vertica uses standard conforming strings as specified in the SQL standard. In standard conforming strings, backslashes are treated as string literals (ordinary characters), not escape characters.

Note: Text read in from files or streams (such as the data inserted using the COPY statement) are not treated as literal strings. The COPY command defines its own escape characters for the data it reads. See the COPY statement documentation for details.

The following options are available, but OpenText recommends that you migrate your application to use standard conforming strings at your earliest convenience, after warnings have been addressed.

The two sections that follow help you identify issues between Vertica 3.5 and 4.0.

Identifying Strings That Are Not Standard Conforming

The following procedure can be used to identify nonstandard conforming strings in your application so that you can convert them into standard conforming strings:

  1. Be sure the StandardConformingStrings parameter is off, as described in Internationalization Parameters in the Administrator's Guide. 

    => ALTER DATABASE mydb SET StandardConformingStrings = 0;

    Note: OpenText recommends that you migrate your application to use Standard Conforming Strings at your earliest convenience.

  2. Turn on the EscapeStringWarning parameter. (ON is the default in Vertica Version 4.0 and later.)

    => ALTER DATABASE mydb SET EscapeStringWarning = 1;

    Vertica now returns a warning each time it encounters an escape string within a string literal. For example, Vertica interprets the \n in the following example as a new line:

    => SELECT 'a\nb';
   WARNING:  nonstandard use of escape in a string literal at character 8
   HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
 ?column?
----------
 a
b
(1 row)

    When StandardConformingStrings is ON, the string is interpreted as four characters: a \ n b.

    Modify each string that Vertica flags by extending it as in the following example:

    E'a\nb'

    Or if the string has quoted single quotes, double them; for example, 'one'' double'.

  3. Turn on the StandardConformingStrings parameter for all sessions:

    => ALTER DATABASE mydb SET StandardConformingStrings = 1;

Doubled Single Quotes

This section discusses vsql inputs that are not passed on to the server.

Vertica recognizes two consecutive single quotes within a string literal as one single quote character. For example, the following inputs, 'You''re here!' ignored the second consecutive quote and returns the following: 

=> SELECT 'You''re here!';
   ?column?
--------------
 You're here!
(1 row)

This is the SQL standard representation and is preferred over the form, 'You\'re here!', because backslashes are not parsed as before. You need to escape the backslash:

=> SELECT (E'You\'re here!');
   ?column?
--------------
 You're here!
(1 row)

This behavior change introduces a potential incompatibility in the use of the vsql \set command, which automatically concatenates its arguments. For example, the following works in both Vertica 3.5 and 4.0:

\set file  '\''  `pwd`  '/file.txt'  '\''\echo :file

vsql takes the four arguments and outputs the following:

'/home/vertica/file.txt'

In Vertica 3.5 the above \set file command could be written all with the arguments run together, but in 4.0 the adjacent single quotes are now parsed differently:

\set file  '\''`pwd`'/file.txt''\''\echo :file
'/home/vertica/file.txt''

Note the extra single quote at the end. This is due to the pair of adjacent single quotes together with the backslash-quoted single quote.

The extra quote can be resolved either as in the first example above, or by combining the literals as follows:

\set file '\''`pwd`'/file.txt'''\echo :file
'/home/vertica/file.txt'

In either case the backslash-quoted single quotes should be changed to doubled single quotes as follows:

\set file '''' `pwd` '/file.txt'''

Additional Examples

=> SELECT 'This \is a string';
     ?column?
------------------
 This \is a string
(1 row)
=> SELECT E'This \is a string';
     ?column?
------------------
 This is a string
=> SELECT E'This is a \n new line';
       ?column?
----------------------
 This is a
 new line
(1 row)
=> SELECT 'String''s characters';
      ?column?
--------------------
 String's characters
(1 row)