CODEWITHSUNDEEP
sqlpostgresql
Hates_
Hates_

Reputation: 68671

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

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?

Upvotes: 11

Views: 17472

Answers (4)

sigma
sigma

Reputation: 29

You have to replace the backslash on your query with a double one, like this:

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

Still you may get a nonstandard use of \\ in a string literal warning, but the query will be executed.
Tested in Postgres 8.4.

Upvotes: 2

Cade Roux
Cade Roux

Reputation: 89651

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

Upvotes: 5

Matthew Wright
Matthew Wright

Reputation: 199

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

Upvotes: 2

Stephen Denne
Stephen Denne

Reputation: 37007

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 '!'

Upvotes: 18

Related Questions