9.7.1. LIKE

9.7.1. LIKE

9.7.1. LIKE

 

string LIKE pattern [ESCAPE escape-character]

string NOT LIKE pattern [ESCAPE escape-character]

 

The LIKE expression returns true if the string matches the supplied pattern . (As expected, the  NOT LIKE expression returns false if LIKE returns true, and vice versa. An equivalent expression  is NOT ( string LIKE pattern ) .)

如果字符串与提供的模式匹配，则LIKE表达式返回true。（按预期，如果LIKE返回true，则NOT LIKE表达式返回false，反之亦然。等效表达式为 NOT ( string LIKE pattern ) 。）

 

If pattern does not contain percent signs or underscores, then the pattern only represents the string  itself; in that case LIKE acts like the equals operator. An underscore ( _ ) in pattern stands for  (matches) any single character; a percent sign ( % ) matches any sequence of zero or more characters.

如果pattern不包含百分号或下划线，则pattern仅表示字符串本身； 在这种情况下，LIKE的行为类似于equals运算符。 模式中的下划线（_）表示（匹配）任何单个字符； 百分号（％）匹配零个或多个字符的任何排列。

 

Some examples:

示例：

 

'abc' LIKE 'abc' true

'abc' LIKE 'a%' true

'abc' LIKE '_b_' true

'abc' LIKE 'c' false

 

LIKE pattern matching always covers the entire string. Therefore, if it's desired to match a sequence  anywhere within a string, the pattern must start and end with a percent sign.

LIKE模式匹配始终覆盖整个字符串。 因此，如果希望在字符串中的任意位置匹配一个序列，则该模式必须以百分号开头和结尾。

 

To match a literal underscore or percent sign without matching other characters, the respective character  in pattern must be preceded by the escape character. The default escape character is the backslash  but a different one can be selected by using the ESCAPE clause. To match the escape character  itself, write two escape characters.

若要匹配文字下划线或百分号而又不匹配其他字符，则模式中的各个字符必须以转义字符开头。 默认的转义字符是反斜杠，但可以使用ESCAPE子句选择其他转义字符。 要匹配转义符本身，需写两个转义符。

 

Note

注

If you have standard_conforming_strings turned off, any backslashes you write in literal  string constants will need to be doubled. See Section 4.1.2.1 for more information.

如果关闭了standard_conforming_strings，则用文字字符串常量编写的任何反斜杠都需要加倍。 有关更多信息，请参见第4.1.2.1节。

 

It's also possible to select no escape character by writing ESCAPE '' . This effectively disables  the escape mechanism, which makes it impossible to turn off the special meaning of underscore and  percent signs in the pattern.

通过使用ESCAPE''也可以选择不使用转义字符。 这有效地禁用了转义机制，当然也使得无法关闭模式中下划线和百分号的特殊含义。

 

The key word ILIKE can be used instead of LIKE to make the match case-insensitive according to  the active locale. This is not in the SQL standard but is a PostgreSQL extension.

根据语言环境，可以使用关键字ILIKE代替LIKE来使匹配不区分大小写。这不是SQL标准的一部分，而是PostgreSQL的扩展。

 

The operator ~~ is equivalent to LIKE , and ~~* corresponds to ILIKE . There are also !~~ and !  ~~* operators that represent NOT LIKE and NOT ILIKE , respectively. All of these operators are  PostgreSQL-specific.

运算符~~等同于LIKE，~~ *等同于ILIKE。 还有!~~和!~~ *运算符分别代表NOT LIKE和NOT ILIKE。 所有这些运算符都是PostgreSQL特有的。

 

There is also the prefix operator ^@ and corresponding starts_with function which covers cases  when only searching by beginning of the string is needed.

还有前缀运算符^ @和相应的starts_with函数，该函数涵盖仅需要按字符串开头进行搜索的情况。