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