高斯数据库 (gaussDB) - SQL 参考 - 函数和操作符 (4)

模式匹配操作符

数据库提供了三种独立的实现模式匹配的方法:SQL LIKE 操作符、SIMILAR TO 操作
符和 POSIX- 风格的正则表达式。除了这些基本的操作符外,还有一些函数可用于提取
或替换匹配子串并在匹配位置分离一个串。

  • LIKE
    描述:判断字符串是否能匹配上 LIKE 后的模式字符串。如果字符串与提供的模式
    匹配,则 LIKE 表达式返回为真(NOT LIKE 表达式返回假),否则返回为假
    (NOT LIKE 表达式返回真)。
    匹配规则:
    a. 此操作符只有在它的模式匹配整个串的时候才能成功。如果要匹配在串内任
    何位置的序列,该模式必须以百分号开头和结尾。
    b. 下划线 (_)代表(匹配)任何单个字符; 百分号(%)代表任意串的通配
    符。
    c. 要匹配文本里的下划线或者百分号,在提供的模式里相应字符必须前导逃逸
    字符。逃逸字符的作用是禁用元字符的特殊含义,缺省的逃逸字符是反斜
    线,也可以用 ESCAPE 子句指定一个不同的逃逸字符。
    d. 要匹配逃逸字符本身,写两个逃逸字符。例如要写一个包含反斜线的模式常
    量,那你就要在 SQL 语句里写两个反斜线。
    说明
    参数 standard_conforming_strings 设置为 off 时,在文串常量中写的任何反斜线都需要被
    双写。因此,写一个匹配单个反斜线的模式实际上要在语句里写四个反斜线。(你可
    以通过用 ESCAPE 选择一个不同的逃逸字符来避免这种情况,这样反斜线就不再是
    LIKE 的特殊字符了。但仍然是字符文本分析器的特殊字符,所以你还是需要两个反斜
    线。)我们也可以通过写 ESCAPE '' 的方式不选择逃逸字符,这样可以有效地禁用逃
    逸机制,但是没有办法关闭下划线和百分号在模式中的特殊含义。
    e. 关键字 ILIKE 可以用于替换 LIKE,区别是 LIKE 大小写敏感,ILIKE 大小写不
    敏感。
    f. 操作符等效于 LIKE,操作符* 等效于 ILIKE。
    示例:
SELECT 'abc' LIKE 'abc' AS RESULT;
result
-----------
t
(1 row)
SELECT 'abc' LIKE 'a%' AS RESULT;
result
-----------
t
(1 row)
SELECT 'abc' LIKE '_b_' AS RESULT;
result
-----------
t
(1 row)
SELECT 'abc' LIKE 'c' AS RESULT;
result
-----------
f
(1 row)
  • SIMILAR TO
    描述:SIMILAR TO 操作符根据自己的模式是否匹配给定串而返回真或者假。他和
    LIKE 非常类似,只不过他使用 SQL 标准定义的正则表达式理解模式。
    匹配规则:
    a. 和 LIKE 一样,此操作符只有在它的模式匹配整个串的时候才能成功。如果要
    匹配在串内任何位置的序列,该模式必须以百分号开头和结尾。
    b. 下划线 (_)代表(匹配)任何单个字符; 百分号(%)代表任意串的通配
    符。
    c. SIMILAR TO 也支持下面这些从 POSIX 正则表达式借用的模式匹配元字符。
    image.png
    image.png
    d. 前导逃逸字符可以禁止所有这些元字符的特殊含义。逃逸字符的使用规则和
    LIKE 一样。
    正则表达式函数:
    支持使用函数 substring(string from pattern for escape) 截取匹配 SQL 正则表达式的
    子字符串。
    示例:
SELECT 'abc' SIMILAR TO 'abc' AS RESULT;
result
-----------
t
(1 row)
SELECT 'abc' SIMILAR TO 'a' AS RESULT;
result
-----------
f
(1 row)
SELECT 'abc' SIMILAR TO '%(b|d)%' AS RESULT;
result
-----------
t
(1 row)
SELECT 'abc' SIMILAR TO '(b|c)%' AS RESULT;
result
-----------
f
(1 row)
  • POSIX 正则表达式
    描述:正则表达式是一个字符序列,它是定义一个串集合 (一个正则集)的缩
    写。 如果一个串是正则表达式描述的正则集中的一员时, 我们就说这个串匹配该
    正则表达式。 POSIX 正则表达式提供了比 LIKE 和 SIMILAR TO 操作符更强大的含
    义。 表 1 正则表达式匹配操作符列出了所有可用于 POSIX 正则表达式模式匹配的
    操作符。
    image.png
    匹配规则:

a. 与 LIKE 不同,正则表达式允许匹配串里的任何位置,除非该正则表达式显式
地挂接在串的开头或者结尾。
b. 除了上文提到的元字符外, POSIX 正则表达式还支持下列模式匹配元字符。
image.png
正则表达式函数:

POSIX 正则表达式支持下面函数。
– substring(string from pattern) 函数提供了抽取一个匹配 POSIX 正则表达式模
式的子串的方法。
– regexp_replace 函数提供了将匹配 POSIX 正则表达式模式的子串替换为新文本
的功能。
– regexp_matches 函数返回一个文本数组,该数组由匹配一个 POSIX 正则表达
式模式得到的所有被捕获子串构成。
– regexp_split_to_table 函数把一个 POSIX 正则表达式模式当作一个定界符来分
离一个串。
– regexp_split_to_array 和 regexp_split_to_table 类似,是一个正则表达式分离函
数,不过它的结果以一个 text 数组的形式返回。
说明
正则表达式分离函数会忽略零长度的匹配,这种匹配发生在串的开头或结尾或者正好
发生在前一个匹配之后。这和正则表达式匹配的严格定义是相悖的,后者由
regexp_matches 实现,但是通常前者是实际中最常用的行为。
示例:

SELECT 'abc'  ~ 'Abc' AS RESULT;
result
--------
f
(1 row)
SELECT 'abc'  ~ * Abc' AS RESULT;
result
--------
t
(1 row)
SELECT 'abc' ! ~ 'Abc' AS RESULT;
result
--------
t
(1 row)
SELECT 'abc'! ~ * 'Abc' AS RESULT;
result
--------
f
(1 row)
SELECT 'abc'  ~ '^a' AS RESULT;
result
--------
t
(1 row)
SELECT 'abc'  ~ '(b|d)'AS RESULT;
result
--------
t
(1 row)
SELECT 'abc'  ~ '^(b|c)'AS RESULT;
result
--------
f
(1 row)

虽然大部分的正则表达式搜索都能很快地执行,但是正则表达式仍可能被人为地
弄成需要任意长的时间和任意量的内存进行处理。不建议从非安全模式来源接受
正则表达式搜索模式,如果必须这样做,建议加上语句超时限制。使用 SIMILAR
TO 模式的搜索具有同样的安全性危险, 因为 SIMILAR TO 提供了很多和 POSIX- 风
格正则表达式相同的能力。LIKE 搜索比其他两种选项简单得多,因此在接受非安
全模式来源搜索时要更安全些。

  
    展开阅读全文