Re: Search for underscore w/ LIKE

brianb-pgsql(at)edsamail(dot)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