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