PostgreSQL 搜索中的特殊字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7580866/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Special character in Search in PostgreSQL
提问by Siva
I am trying to retrieve the contact names based on the keyed search string. It performs good for the English alphabets and behaves strangely for special chars such as _,/,\,%
我正在尝试根据键入的搜索字符串检索联系人姓名。它对英文字母表现良好，对特殊字符如 _,/,\,% 表现奇怪
My query in the function is similar to
我在函数中的查询类似于
SELECT contact_name FROM contacts WHERE LOWER(contact_name) LIKE LOWER('_McDonald%') ORDER BY LOWER(contact_name) ASC LIMIT 1;
It looks like, during searching, it retrieves all the contact names instead of the desired. The similar bizarre happens for the above mentioned special chars. I need to support these. How do I educate postgres to consider these chars? Please guide me.
看起来，在搜索过程中，它会检索所有联系人姓名而不是所需的姓名。上面提到的特殊字符也发生了类似的奇怪现象。我需要支持这些。我如何教育 postgres 考虑这些字符？请指导我。
Thanks and Regards, Siva.
谢谢和问候，西瓦。
回答by Haes
I don't know what PostgreSQL version you are using, but make sure you escape any special characters (_, %, \, ...) if you want to match them literally as it is described in the docs.
我不知道您使用的是什么 PostgreSQL 版本，但是如果您想按照文档中的描述逐字匹配它们，请确保对任何特殊字符（_、%、\、...）进行转义。
Assuming you want to search for any contact names that start with _McDonald:
假设您要搜索任何以 开头的联系人姓名_McDonald：
SELECT contact_name FROM contacts
WHERE LOWER(contact_name) LIKE LOWER(E'\_McDonald%')
ORDER BY LOWER(contact_name) ASC LIMIT 1;
回答by a_horse_with_no_name
Just to complete the picture:
只是为了完成图片：
You can also use the ESCAPE clause to specify the character for escaping the wildcards. This is useful if you'd like to include e.g. a \in your search string.
您还可以使用 ESCAPE 子句指定用于转义通配符的字符。如果您想\在搜索字符串中包含例如 a ，这将非常有用。
SELECT contact_name
FROM contacts
WHERE LOWER(contact_name) LIKE LOWER('@_McDonald%') ESCAPE '@'
ORDER BY LOWER(contact_name) ASC
LIMIT 1;
Btw: I would recommend to turn on the configuration option standard_conforming_stringsto avoid confusion (Haes' answer avoids this problem by using the E'syntax).
Since 9.1 this is the default mode anyway.
顺便说一句：我建议打开配置选项standard_conforming_strings以避免混淆（Haes 的回答通过使用E'语法避免了这个问题）。从 9.1 开始，无论如何这是默认模式。
回答by aabiro
You can use a prepended E in the where clause before the string using doublebackslashes to escape the following character:
您可以在字符串之前的 where 子句中使用前置 E 使用双反斜杠来转义以下字符：
WHERE LOWER(contact_name) LIKE LOWER(E'\_McDonald%')
ORyou may do the appended escape clause as below:
或者您可以执行以下附加的转义子句：
WHERE LOWER(contact_name) LIKE LOWER('@_McDonald%') ESCAPE '@'
Note the former method will work with the clauses LIKE, SIMILAR TO and NOT SIMILAR TO, but the above method will work as well with more powerful POSIX regular expressions like ~, ~, !~, !~
请注意，前一种方法适用于 LIKE、SIMILAR TO 和 NOT SIMILAR TO 子句，但上述方法适用于更强大的 POSIX 正则表达式，如 ~, ~ , !~, !~
In the docsyou can also see some functions that may be of use as well such as regexp_replacewhere you could try this to escape special characters in the contact_name field:
在文档中，您还可以看到一些可能有用的函数，例如regexp_replace您可以尝试在 contact_name 字段中转义特殊字符的地方：
SELECT
regexp_replace(contact_name, '([!$()*+.:<=>?[\\]^{|}-])', '\', 'g')
FROM
contacts
WHERE
LOWER(contact_name) LIKE LOWER('_McDonald%')
ORDER BY
LOWER(contact_name) ASC LIMIT 1;
