Escaping an underscore in LIKE - Mailing list pgsql-general

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


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