Postgresql - Pattern Matching

There are three separate approaches to pattern matching provided by PostgreSQL: the traditional SQL LIKE operator, the more recent SIMILAR TO operator (added in SQL:1999), and POSIX-style regular expressions. Aside from the basic "does this string match this pattern?" operators, functions are available to extract or replace matching substrings and to split a string at matching locations.

Tip: If you have pattern matching needs that go beyond this, consider writing a user-defined function in Perl or Tcl.

9.7.1. LIKE

stringLIKEpattern[ESCAPEescape-character]stringNOT LIKEpattern[ESCAPEescape-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).)

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.

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, to match a sequence anywhere within a string, the pattern must start and end with a percent sign.

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.

Note that the backslash already has a special meaning in string literals, so to write a pattern constant that contains a backslash you must write two backslashes in an SQL statement (assuming escape string syntax is used, see ). Thus, writing a pattern that actually matches a literal backslash means writing four backslashes in the statement. You can avoid this by selecting a different escape character with ESCAPE; then a backslash is not special to LIKE anymore. (But backslash is still special to the string literal parser, so you still need two of them to match a backslash.)

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.

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.

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.

9.7.2. SIMILAR TO Regular Expressions

stringSIMILAR TOpattern[ESCAPEescape-character]stringNOT SIMILAR TOpattern[ESCAPEescape-character]

The SIMILAR TO operator returns true or false depending on whether its pattern matches the given string. It is similar to LIKE, except that it interprets the pattern using the SQL standard‘s definition of a regular expression. SQL regular expressions are a curious cross between LIKE notation and common regular expression notation.

Like LIKE, the SIMILAR TO operator succeeds only if its pattern matches the entire string; this is unlike common regular expression behavior where the pattern can match any part of the string. Also like LIKE, SIMILAR TO uses _ and % as wildcard characters denoting any single character and any string, respectively (these are comparable to . and .* in POSIX regular expressions).

In addition to these facilities borrowed from LIKE, SIMILAR TO supports these pattern-matching metacharacters borrowed from POSIX regular expressions:

• | denotes alternation (either of two alternatives).

• * denotes repetition of the previous item zero or more times.

• + denotes repetition of the previous item one or more times.

• ? denotes repetition of the previous item zero or one time.

• {m} denotes repetition of the previous item exactly m times.

• {m,} denotes repetition of the previous item m or more times.

• {m,n} denotes repetition of the previous item at least m and not more than n times.

• Parentheses () can be used to group items into a single logical item.

• A bracket expression [...] specifies a character class, just as in POSIX regular expressions.

Notice that the period (.) is not a metacharacter for SIMILAR TO.

As with LIKE, a backslash disables the special meaning of any of these metacharacters; or a different escape character can be specified with ESCAPE.

Some examples:

‘abc‘ SIMILAR TO ‘abc‘ true ‘abc‘ SIMILAR TO ‘a‘ false ‘abc‘ SIMILAR TO ‘%(b|d)%‘ true ‘abc‘ SIMILAR TO ‘(b|c)%‘ false

The substring function with three parameters, substring(string from pattern for escape-character), provides extraction of a substring that matches an SQL regular expression pattern. As with SIMILAR TO, the specified pattern must match the entire data string, or else the function fails and returns null. To indicate the part of the pattern that should be returned on success, the pattern must contain two occurrences of the escape character followed by a double quote ("). The text matching the portion of the pattern between these markers is returned.

Some examples, with #" delimiting the return string:

substring(‘foobar‘ from ‘%#"o_b#"%‘ for ‘#‘) oob substring(‘foobar‘ from ‘#"o_b#"%‘ for ‘#‘) NULL

9.7.3. POSIX Regular Expressions

 lists the available operators for pattern matching using POSIX regular expressions.

Table 9-11. Regular Expression Match Operators

Option	Description
b	rest of RE is a BRE
c	case-sensitive matching (overrides operator type)
e	rest of RE is an ERE
i	case-insensitive matching (see ) (overrides operator type)
m	historical synonym for n
n	newline-sensitive matching (see )
p	partial newline-sensitive matching (see )
q	rest of RE is a literal ("quoted") string, all ordinary characters
s	non-newline-sensitive matching (default)
t	tight syntax (default; see below)
w	inverse partial newline-sensitive ("weird") matching (see )
x	expanded syntax (see below)

Embedded options take effect at the ) terminating the sequence. They can appear only at the start of an ARE (after the ***: director if any).

In addition to the usual (tight) RE syntax, in which all characters are significant, there is an expanded syntax, available by specifying the embedded x option. In the expanded syntax, white-space characters in the RE are ignored, as are all characters between a # and the following newline (or the end of the RE). This permits paragraphing and commenting a complex RE. There are three exceptions to that basic rule:

• a white-space character or # preceded by \ is retained

• white space or # within a bracket expression is retained

• white space and comments cannot appear within multi-character symbols, such as (?:

For this purpose, white-space characters are blank, tab, newline, and any character that belongs to the space character class.

Finally, in an ARE, outside bracket expressions, the sequence (?#ttt) (where ttt is any text not containing a )) is a comment, completely ignored. Again, this is not allowed between the characters of multi-character symbols, like (?:. Such comments are more a historical artifact than a useful facility, and their use is deprecated; use the expanded syntax instead.

None of these metasyntax extensions is available if an initial ***= director has specified that the user‘s input be treated as a literal string rather than as an RE.

9.7.3.5. Regular Expression Matching Rules

In the event that an RE could match more than one substring of a given string, the RE matches the one starting earliest in the string. If the RE could match more than one substring starting at that point, either the longest possible match or the shortest possible match will be taken, depending on whether the RE is greedy or non-greedy.

Whether an RE is greedy or not is determined by the following rules:

• Most atoms, and all constraints, have no greediness attribute (because they cannot match variable amounts of text anyway).

• Adding parentheses around an RE does not change its greediness.

• A quantified atom with a fixed-repetition quantifier ({m} or {m}?) has the same greediness (possibly none) as the atom itself.

• A quantified atom with other normal quantifiers (including {m,n} with m equal to n) is greedy (prefers longest match).

• A quantified atom with a non-greedy quantifier (including {m,n}? with m equal to n) is non-greedy (prefers shortest match).

• A branch — that is, an RE that has no top-level | operator — has the same greediness as the first quantified atom in it that has a greediness attribute.

• An RE consisting of two or more branches connected by the | operator is always greedy.

The above rules associate greediness attributes not only with individual quantified atoms, but with branches and entire REs that contain quantified atoms. What that means is that the matching is done in such a way that the branch, or whole RE, matches the longest or shortest possible substring as a whole. Once the length of the entire match is determined, the part of it that matches any particular subexpression is determined on the basis of the greediness attribute of that subexpression, with subexpressions starting earlier in the RE taking priority over ones starting later.

An example of what this means:

SELECT SUBSTRING(‘XY1234Z‘, ‘Y*([0-9]{1,3})‘); Result: 123SELECT SUBSTRING(‘XY1234Z‘, ‘Y*?([0-9]{1,3})‘); Result: 1

In the first case, the RE as a whole is greedy because Y* is greedy. It can match beginning at the Y, and it matches the longest possible string starting there, i.e., Y123. The output is the parenthesized part of that, or 123. In the second case, the RE as a whole is non-greedy because Y*? is non-greedy. It can match beginning at the Y, and it matches the shortest possible string starting there, i.e., Y1. The subexpression [0-9]{1,3} is greedy but it cannot change the decision as to the overall match length; so it is forced to match just 1.

In short, when an RE contains both greedy and non-greedy subexpressions, the total match length is either as long as possible or as short as possible, according to the attribute assigned to the whole RE. The attributes assigned to the subexpressions only affect how much of that match they are allowed to "eat" relative to each other.

The quantifiers {1,1} and {1,1}? can be used to force greediness or non-greediness, respectively, on a subexpression or a whole RE.

Match lengths are measured in characters, not collating elements. An empty string is considered longer than no match at all. For example: bb* matches the three middle characters of abbbc; (week|wee)(night|knights) matches all ten characters of weeknights; when (.*).* is matched against abc the parenthesized subexpression matches all three characters; and when (a*)* is matched against bc both the whole RE and the parenthesized subexpression match an empty string.

If case-independent matching is specified, the effect is much as if all case distinctions had vanished from the alphabet. When an alphabetic that exists in multiple cases appears as an ordinary character outside a bracket expression, it is effectively transformed into a bracket expression containing both cases, e.g., x becomes [xX]. When it appears inside a bracket expression, all case counterparts of it are added to the bracket expression, e.g., [x] becomes [xX] and [^x] becomes [^xX].

If newline-sensitive matching is specified, . and bracket expressions using ^ will never match the newline character (so that matches will never cross newlines unless the RE explicitly arranges it) and ^and $ will match the empty string after and before a newline respectively, in addition to matching at beginning and end of string respectively. But the ARE escapes \A and \Z continue to match beginning or end of string only.

If partial newline-sensitive matching is specified, this affects . and bracket expressions as with newline-sensitive matching, but not ^ and $.

If inverse partial newline-sensitive matching is specified, this affects ^ and $ as with newline-sensitive matching, but not . and bracket expressions. This isn‘t very useful but is provided for symmetry.

9.7.3.6. Limits and Compatibility

No particular limit is imposed on the length of REs in this implementation. However, programs intended to be highly portable should not employ REs longer than 256 bytes, as a POSIX-compliant implementation can refuse to accept such REs.

The only feature of AREs that is actually incompatible with POSIX EREs is that \ does not lose its special significance inside bracket expressions. All other ARE features use syntax which is illegal or has undefined or unspecified effects in POSIX EREs; the *** syntax of directors likewise is outside the POSIX syntax for both BREs and EREs.

Many of the ARE extensions are borrowed from Perl, but some have been changed to clean them up, and a few Perl extensions are not present. Incompatibilities of note include \b, \B, the lack of special treatment for a trailing newline, the addition of complemented bracket expressions to the things affected by newline-sensitive matching, the restrictions on parentheses and back references in lookahead constraints, and the longest/shortest-match (rather than first-match) matching semantics.

Two significant incompatibilities exist between AREs and the ERE syntax recognized by pre-7.4 releases of PostgreSQL:

• In AREs, \ followed by an alphanumeric character is either an escape or an error, while in previous releases, it was just another way of writing the alphanumeric. This should not be much of a problem because there was no reason to write such a sequence in earlier releases.

• In AREs, \ remains a special character within [], so a literal \ within a bracket expression must be written \\.

9.7.3.7. Basic Regular Expressions

BREs differ from EREs in several respects. In BREs, |, +, and ? are ordinary characters and there is no equivalent for their functionality. The delimiters for bounds are \{ and \}, with { and } by themselves ordinary characters. The parentheses for nested subexpressions are \( and \), with (and ) by themselves ordinary characters. ^ is an ordinary character except at the beginning of the RE or the beginning of a parenthesized subexpression, $ is an ordinary character except at the end of the RE or the end of a parenthesized subexpression, and * is an ordinary character if it appears at the beginning of the RE or the beginning of a parenthesized subexpression (after a possible leading ^). Finally, single-digit back references are available, and \< and \> are synonyms for [[:<:]] and [[:>:]] respectively; no other escapes are available in BREs.

PostgreSQL中提供了三种实现模式匹配的方法：SQL LIKE操作符，更近一些的SIMILAR TO操作符，和POSIX-风格正则表达式。
    1. LIKE：
    string LIKE pattern [ ESCAPE escape-character ]
    string NOT LIKE pattern [ ESCAPE escape-character ]
    每个pattern定义一个字串的集合。如果该string包含在pattern代表的字串集合里，那么LIKE表达式返回真。和我们想象的一样，如果 LIKE返回真，那么NOT LIKE表达式返回假，反之亦然。在pattern里的下划线(_)代表匹配任何单个字符，而一个百分号(%)匹配任何零或更多字符，如：
    ‘abc‘ LIKE ‘abc‘     true
    ‘abc‘ LIKE ‘a%‘     true
    ‘abc‘ LIKE ‘_b_‘    true
    ‘abc‘ LIKE ‘c‘        false  
    要匹配文本的下划线或者百分号，而不是匹配其它字符，在pattern里相应的字符必须前导转义字符。缺省的转义字符是反斜杠，但是你可以用ESCAPE子句指定一个。要匹配转义字符本身，写两个转义字符。我们也可以通过写成ESCAPE ‘‘的方式有效地关闭转义机制，此时，我们就不能关闭下划线和百分号的特殊含义了。
    关键字ILIKE可以用于替换LIKE，令该匹配就当前的区域设置是大小写无关的。这个特性不是SQL标准，是PostgreSQL的扩展。操作符~~等效于LIKE， 而~~*对应ILIKE。还有!~~和!~~*操作符分别代表NOT LIKE和NOT ILIKE。所有这些操作符都是PostgreSQL特有的。

    2. SIMILAR TO正则表达式：
    SIMILAR TO根据模式是否匹配给定的字符串而返回真或者假。
    string SIMILAR TO pattern [ESCAPE escape-character]
    string NOT SIMILAR TO pattern [ESCAPE escape-character]
    它和LIKE非常类似，支持LIKE的通配符(‘_‘和‘%‘)且保持其原意。除此之外，SIMILAR TO还支持一些自己独有的元字符，如：    
    1). | 标识选择(两个候选之一)。
    2). * 表示重复前面的项零次或更多次。
    3). + 表示重复前面的项一次或更多次。
    4). 可以使用圆括弧()把项组合成一个逻辑项。
    5). 一个方括弧表达式[...]声明一个字符表，就像POSIX正则表达式一样。
    见如下示例：
    ‘abc‘ SIMILAR TO ‘abc‘           true
    ‘abc‘ SIMILAR TO ‘a‘              false
    ‘abc‘ SIMILAR TO ‘%(b|d)%‘  true
    ‘abc‘ SIMILAR TO ‘(b|c)%‘     false
    带三个参数的substring，substring(string from pattern for escape-character)，提供了一个从字串中抽取一个匹配SQL正则表达式模式的子字串的函数。和SIMILAR TO一样，声明的模式必须匹配整个数据串，否则函数失效并返回NULL。为了标识在成功的时候应该返回的模式部分，模式必须出现后跟双引号(")的两个转 义字符。匹配这两个标记之间的模式的字串将被返回，如：
    MyTest=# SELECT substring(‘foobar‘ from ‘%#"o_b#"%‘ FOR ‘#‘); --这里#是转义符，双引号内的模式是返回部分。
     substring
    -----------
     oob
    (1 row)
    MyTest=# SELECT substring(‘foobar‘ from ‘#"o_b#"%‘ FOR ‘#‘);  --foobar不能完全匹配后面的模式，因此返回NULL。
     substring
    -----------