为什么我的LIKE'％\ _'查询会返回所有行，而不仅仅是以下划线结尾？

I currently have the query running on Postgres:

我目前在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?

我究竟做错了什么?

4 个解决方案

#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.

你的反斜杠是不是通过PostgreSQL?如果您将字符串传递给另一个将反斜杠视为转义字符(例如Java字符串)的图层,那么该图层可能会删除反斜杠,您可能需要转义该图层的反斜杠。

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

你还有更多的单字符用户名吗?如果反斜杠没有通过PostgreSQL,那么它们也会匹配'_'

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

您可以尝试使用ESCAPE子句:用户名LIKE'%!_'ESCAPE'!'

#2

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

“_”是大多数SQL变体中的单字符通配符。如果要匹配实际的“_”字符,则必须转义它。

#3

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

我用过postgres已经有一段时间了,但我认为你可以'%[_]'来获得你想要的东西。这肯定适用于SQL服务器,虽然我现在没有postgres数据库设置来尝试它

#4

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.

您必须使用双精度替换查询中的反斜杠,如下所示:SELECT * FROM addenda.users WHERE users.username LIKE'%\\ _'仍然可以在字符串文字警告中非标准地使用\\但查询将被执行。在Postgres 8.4中测试过。

#1