9.7.2. SIMILAR TO Regular Expressions

9.7.2. SIMILAR TO Regular Expressions

9.7.2.SIMILAR TO正则表达式

 

string SIMILAR TO pattern [ESCAPE escape-character]

string NOT SIMILAR TO pattern [ESCAPE escape-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.

SIMILAR TO运算符根据其模式是否与给定的字符串匹配而返回true或false。它类似于LIKE，不同之处在于它使用SQL标准的正则表达式定义来解释模式。 SQL正则表达式是LIKE表示法和通用正则表达式表示法的一个综合。

 

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

像LIKE一样，SIMILAR TO运算符仅在其模式与整个字符串匹配时才成功；这与常见的正则表达式行为不同，在常规行为中，模式可以匹配字符串的任何部分。就像LIKE一样，SIMILAR TO使用_和％作为通配符，分别表示任何单个字符和任何字符串（它们与POSIX正则表达式中的.和.*相当）。

 

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

除了从LIKE借用的这些功能之外，SIMILAR TO还支持从POSIX正则表达式借用的这些模式匹配元字符：

• | 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}表示前一个项目正好重复m次。

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

•{m，}表示前一项重复m次或更多次。

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

•{m，n}表示前一项重复至少m次且不超过n次。

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

•与POSIX正则表达式一样，括号表达式[...]指定字符类。

 

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

请注意，句点（.）不是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 .

与LIKE一样，反斜杠会禁用所有这些元字符的特殊含义； 或可以使用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.

带有三个参数的子字符串函数substring（来自转义字符的模式的字符串）提供与SQL正则表达式模式匹配的子字符串的提取功能。 与SIMILAR TO一样，指定的模式必须匹配整个数据字符串，否则函数将失败并返回null。 为了指示成功时应返回的模式部分，该模式必须包含两次出现的转义字符，后跟双引号（“）。该函数将返回与这些标记之间的模式部分匹配的文本。

 

Some examples, with #" delimiting the return string:

使用#作为分隔符的示例：

 

substring('foobar' from '%#"o_b#"%' for '#') oob

substring('foobar' from '#"o_b#"%' for '#') NULL