Re: unc paths, like and backslashes on 8.4

Chris Curvey wrote:
> 1) Why do I get a warning when doubling a backslash?
> 2) What is the meaning of "E" syntax (E'\\\\fs1\\bar')?
> 3) If I have backslashes in my table, how can I get them back out?
> 4) I'd like to run an update to change the value '\\fs1\bar' to
\\fs1\foo\bar'. What incantation
> would do that.
>
> So, trying to figure it out on my own...
>
> CREATE TABLE FOOBAR
> ( UNC_PATH VARCHAR(100)
> );
>
> /* first insert attempt */
> INSERT INTO FOOBAR VALUES ('\\FS1\BAR');
>
> returns a warning:
>
> WARNING: nonstandard use of \\ in a string literal
> LINE 1: INSERT INTO FOOBAR VALUES ('\\FS1\BAR');
> ^
> HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
> Query returned successfully: 1 row affected, 21 ms execution time.
>
> but the row is inserted. There is one leading backslash, and the "b"
is some unprintable character.

You have standard_conforming_strings set to "off" and
escape_string_warning set to "on".

So backslash sequences (backslash + something) are not taken literally,
but interpreted as escape sequences.

> Let's try the "E" syntax, whatever that is:
>
> INSERT INTO FOOBAR VALUES (E'\\FS1\BAR');
>
> No warning, but exactly the same results again (one leading backslash,
"b" replaced by unprintable
> char). Let's try E with doubled backslashes:

You are insinuating that the "E" syntax is not well-documented.
Have you tried to read up on it?
What happens here is that you got rid of the warning because
you explicitly said "I'm going to use escape sequences".

> INSERT INTO FOOBAR VALUES (E'\\\\FS1\\BAR');
>
> okay, that worked. Yay. Now let's see if I can get the record back
out with "LIKE":
>
> SELECT * FROM FOOBAR WHERE UNC_PATH LIKE E'\\\\FS1%';
>
> That gets back a record, but the value returned is "\FS1BAR". I'm
missing two backslashes. I'm too
> confused to even attempt the update.

In LIKE expressions, backslash acts as an escape character,
so you have double escaping: once from the LIKE pattern, and
again from standard_conforming_strings.

You'd have to write:

SELECT * FROM FOOBAR WHERE UNC_PATH LIKE E'\\\\\\\\FS1%';

or say that backslash is not an escape character:

SELECT * FROM FOOBAR WHERE UNC_PATH LIKE E'\\\\FS1%' ESCAPE '';

Yours,
Laurenz Albe