Re: LIKE with pattern containing backslash

Jack Orenstein wrote:

> Can someone help in my understanding of what is correct
> behavior (in the literal and bound variable cases)?
> Is there a bug in the driver? in postgres? in the
> docs? Or in my understanding?

LIKE E'%\\%' will match a string that ends with a percent sign, not a
string that contains a backslash.
That's because the backslash acts additionally as the default escape
character for LIKE patterns.
You can add ESCAPE '' after the LIKE statement to avoid that.
Otherwise you get really two levels of different backslash
interpretation here, one for the string parser and one for the LIKE
operator, and that doesn't take into account any additional level
needed if you embed the query into a source code string.
Embedding "like backslash" in a C string is a kind of worst case, you
really need to write:
"LIKE E'\\\\\\\\'" (8 backslashes) which looks ridiculous but is the
correct form.
The C compiler will reduce 8 to 4. The SQL parser will reduce 4 to 2.
The LIKE operator will reduce 2 to 1.

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage:
http://www.manitou-mail.org