Re: Search for underscore w/ LIKE

brianb-pgsql@edsamail.com writes:
> How do I use LIKE to search for strings with an underscore? The
> documentation (well, Bruce's book) says to use 2 underscores (__) but it
> doesn't work.

If Bruce's book says that, I hope it's not too late for him to change
it ;-)

The correct way is to escape the underscore with a backslash.  You
actually have to write two backslashes in your query:
select * from foo where bar like '%\\_baz'

The first backslash quotes the second one for the query parser, so that
what ends up inside the system is %\_baz, and then the LIKE function
knows what to do with that.

Similarly, '\\%' would be the way to match a literal %.  You can
actually backslash-quote any single character this way in LIKE,
but % and _ are the only ones where it makes a difference.  (In the
regexp-matching operators there are many more special characters and
so many more times when you need the backslash trick.)
        regards, tom lane