Re: unc paths, like and backslashes on 8.4

On Sep 26, 2012, at 20:42, Chris Curvey <chris(at)chriscurvey(dot)com> wrote:

> I just don't get how we are supposed to use LIKE with backslashes in strings in 8.4. This is particularly vexing, because I have a field containing UNC paths that I need to search on (and eventually update). I have been looking at this page for guidance: http://www.postgresql.org/docs/8.4/static/functions-matching.html
>
> So I will ask my questions first, then show you what I tried:
>
> 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. 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:
>
> 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.
>
> -Chris
>

First, please read the follow section of the docs, though especially 4.1.2

http://www.postgresql.org/docs/8.4/interactive/sql-syntax-lexical.html

Note the callout regarding standard conforming strings.

Since LIKE is an escapable pattern and you are using it in an escapable string literal the backslashes behave as such:

"\\\\" perform string literal escape -> "\\" perform like escape -> "\"

So on the first pass the four become two since each pair represents a single backslash post-literal-escape. Then the pair supplied to the LIKE becomes one post-like-escape.

Post back here if the reason and behavior of E'' is still unclear after reading the documentation.

David J.