Why does my query with LIKE '%\_' return all rows and not just those ending in an underscore?

Question

I currently have the query running on Postgres:

SELECT * FROM addenda.users WHERE users.username LIKE '%\_'

But rather then returning just entries ending in an underscore, I get all results back, regardless of whether it contains an underscore or not.

Running the query below returns a username which is just an underscore, so the escaping does work:

SELECT * FROM addenda.users WHERE users.username LIKE '\_'

And running the query below returns a username which ends with a specific letter (s):

SELECT * FROM addenda.users WHERE users.username LIKE '%s'

What am I doing wrong?

Answer 1

Is your backslash not getting through to PostgreSQL? If you're passing the string through another layer that treats the backslash as an escape character (e.g. a Java String), then that layer may be removing the backslash, and you might need to escape your backslash for that layer.

Do you have any more single character usernames? If the backslash wasn't getting through to PostgreSQL then they would also match '_'

You might be able to try the ESCAPE clause: username LIKE '%!_' ESCAPE '!'

Answer 2

It's been a while since I've used postgres, but I think you can do '%[_]' to get what you want. That certainly works on SQL server, though I don't have a postgres database setup right now to try it on

Answer 3

"_" is the single-character wildcard in most SQL variants. You HAVE to escape it if you want to match an actual "_" character.