Handling Backslash Escape Characters

[JimKnicely]

In computing and telecommunication, an escape character is a character which invokes an alternative interpretation on subsequent characters in a character sequence. The typical escape character used is the backslash character.

Vertica uses standard conforming strings as specified in the SQL standard, which means that backslashes are treated as string literals, not escape characters.

But what if I want backslashes to be treated as an escape character? One way is to turn off standard conforming strings!

Example:

The “\n” escape sequence typically is translated into a new line character. But in Vertica, by default it’s treated literally as “\n”.

Code: Select all

dbadmin=> SELECT 'New\nLine';
?column?
-----------
New\nLine
(1 row)

If I want the backslash to be treated as an escape character I could set the STANDARD_CONFORMING_STRINGS variable to OFF. It’s ON by default.

Code: Select all

dbadmin=> SET standard_conforming_strings TO off;
SET
 
dbadmin=> SELECT 'New\nLine';
WARNING 4168:  Nonstandard use of escape in a string literal at or near "'New\n" at character 8
HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'
?column?
----------
New
Line
(1 row)

Notice that a new line appears in the output of the query, but what’s up with that ugly warning? We can get rid of that nonsense by setting the ESCAPE_STRING_WARNING variable to OFF. It’s ON by default.

Code: Select all

dbadmin=> SET escape_string_warning TO off;
SET
 
dbadmin=> SELECT 'New\nLine';
?column?
----------
New
Line
(1 row)

Have fun!