Thread: Escaping underscores in LIKE

Am I doing something stupid trying to escape an underscore in LIKE?
Version 7.1b3


richardh=> select * from foo;
  a
-----
 a_c
 a_d
 abc
(3 rows)

richardh=> select * from foo where a like 'a_c';
  a
-----
 a_c
 abc
(2 rows)

richardh=> select * from foo where a like 'a\_c';
  a
-----
 a_c
 abc
(2 rows)

richardh=> select * from foo where a like 'a\_c' escape '\\';
  a
-----
 a_c
 abc
(2 rows)

richardh=> select * from foo where a like 'ax_c' escape 'x';
  a
-----
 a_c
(1 row)

- Richard Huxton

dev@archonet.com writes:
> Am I doing something stupid trying to escape an underscore in LIKE?

You need more backslashes.  Don't forget the string-literal parser eats
one level of backslashes, before LIKE ever gets to see the pattern.

            regards, tom lane

From: "Tom Lane" <tgl@sss.pgh.pa.us>

> dev@archonet.com writes:
> > Am I doing something stupid trying to escape an underscore in LIKE?

Ah so Yes.

> You need more backslashes.  Don't forget the string-literal parser eats
> one level of backslashes, before LIKE ever gets to see the pattern.
>
> regards, tom lane

Thanks Tom, as a 2-stage process it all makes sense. Tried it out in PHP too
and I end up with four backslashes (3 stages).

Another DOH! item for my notes.

- Richard Huxton

"Richard Huxton" <dev@archonet.com> writes:
>> You need more backslashes.  Don't forget the string-literal parser eats
>> one level of backslashes, before LIKE ever gets to see the pattern.

> Thanks Tom, as a 2-stage process it all makes sense. Tried it out in PHP too
> and I end up with four backslashes (3 stages).

> Another DOH! item for my notes.

BTW, in 7.1 you can do

    foo LIKE 'a#_b' ESCAPE '#'

(for '#' use whatever you want) to switch to a different escape
character that's not special to upstream processing.  Probably nicer
than dealing with four or more backslashes ...

            regards, tom lane