On Sep 26, 2012, at 20:42, Chris Curvey <ch...@chriscurvey.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.
