openGauss學習筆記-24 openGauss 簡單數據管理-模式匹配操作符

數據庫提供了三種獨立的實現模式匹配的方法：SQL LIKE操作符、SIMILAR TO操作符和POSIX-風格的正則表達式。除了這些基本的操作符外，還有一些函數可用于提取或替換匹配子串并在匹配位置分離一個串。

24.1 LIKE

描述：判斷字符串是否能匹配上LIKE后的模式字符串。如果字符串與提供的模式匹配，則LIKE表達式返回為真（NOT LIKE表達式返回假），否則返回為假（NOT LIKE表達式返回真）。

匹配規(guī)則：

1. 此操作符只有在它的模式匹配整個串的時候才能成功。如果要匹配在串內任何位置的序列，該模式必須以百分號開頭和結尾。

2. 下劃線 （_）代表（匹配）任何單個字符； 百分號（%）代表任意串的通配符。

3. 要匹配文本里的下劃線或者百分號，在提供的模式里相應字符必須前導逃逸字符。逃逸字符的作用是禁用元字符的特殊含義，缺省的逃逸字符是反斜線，也可以用ESCAPE子句指定一個不同的逃逸字符。

4. 要匹配逃逸字符本身，寫兩個逃逸字符。例如要寫一個包含反斜線的模式常量，那你就要在SQL語句里寫兩個反斜線。

   說明： 參數standard_conforming_strings設置為off時，在文串常量中寫的任何反斜線都需要被雙寫。因此，寫一個匹配單個反斜線的模式實際上要在語句里寫四個反斜線（你可以通過用ESCAPE選擇一個不同的逃逸字符來避免這種情況，這樣反斜線就不再是LIKE的特殊字符了。但仍然是字符文本分析器的特殊字符，所以你還是需要兩個反斜線)。 在兼容MYSQL數據模式時，您也可以通過寫ESCAPE ''的方式不選擇逃逸字符，這樣可以有效地禁用逃逸機制，但是沒有辦法關閉下劃線和百分號在模式中的特殊含義。

5. 關鍵字ILIKE可以用于替換LIKE，區(qū)別是LIKE大小寫敏感，ILIKE大小寫不敏感。

6. 操作符等效于LIKE，操作符*等效于ILIKE。

示例：

openGauss=# SELECT 'abc' LIKE 'abc' AS RESULT;result
-----------t
(1 row)
openGauss=# SELECT 'abc' LIKE 'a%' AS RESULT;result
-----------t
(1 row)
openGauss=# SELECT 'abc' LIKE '_b_' AS RESULT;result
-----------t
(1 row)
openGauss=# SELECT 'abc' LIKE 'c' AS RESULT;result
-----------f
(1 row)

24.2 SIMILAR TO

描述：SIMILAR TO操作符根據自己的模式是否匹配給定串而返回真或者假。他和LIKE非常類似，只不過他使用SQL標準定義的正則表達式理解模式。

匹配規(guī)則：

1. 和LIKE一樣，此操作符只有在它的模式匹配整個串的時候才能成功。如果要匹配在串內任何位置的序列，該模式必須以百分號開頭和結尾。

2. 下劃線 （_）代表（匹配）任何單個字符； 百分號（%）代表任意串的通配符。

3. SIMILAR TO也支持下面這些從POSIX正則表達式借用的模式匹配元字符。

   元字符	含義
   |	表示選擇（兩個候選之一）
   *	表示重復前面的項零次或更多次
   +	表示重復前面的項一次或更多次
   ?	表示重復前面的項零次或一次
   {m}	表示重復前面的項剛好m次
   {m,}	表示重復前面的項m次或更多次
   {m,n}	表示重復前面的項至少m次并且不超過n次
   ()	把多個項組合成一個邏輯項
   […]	聲明一個字符類，就像POSIX正則表達式一樣

4. 前導逃逸字符可以禁止所有這些元字符的特殊含義。逃逸字符的使用規(guī)則和LIKE一樣。

示例：

openGauss=# SELECT 'abc' SIMILAR TO 'abc' AS RESULT;result
-----------t
(1 row)
openGauss=# SELECT 'abc' SIMILAR TO 'a' AS RESULT;result
-----------f
(1 row)
openGauss=# SELECT 'abc' SIMILAR TO '%(b|d)%' AS RESULT;result
-----------t
(1 row)
openGauss=# SELECT 'abc' SIMILAR TO '(b|c)%'  AS RESULT;result
-----------f
(1 row)

24.3 POSIX正則表達式

描述：正則表達式是一個字符序列，它是定義一個串集合（一個正則集）的縮寫。 如果一個串是正則表達式描述的正則集中的一員時， 我們就說這個串匹配該正則表達式。 POSIX正則表達式提供了比LIKE和SIMILAR TO操作符更強大的含義。表1列出了所有可用于POSIX正則表達式模式匹配的操作符。

表 1 正則表達式匹配操作符

操作符	描述	例子
~	匹配正則表達式，大小寫敏感	‘thomas’ ~ ‘.thomas.’
~*	匹配正則表達式，大小寫不敏感	‘thomas’ ~* ‘.Thomas.’
!~	不匹配正則表達式，大小寫敏感	‘thomas’ !~ ‘.Thomas.’
!~*	不匹配正則表達式，大小寫不敏感	‘thomas’ !~* ‘.vadim.’

匹配規(guī)則：

1. 與LIKE不同，正則表達式允許匹配串里的任何位置，除非該正則表達式顯式地掛接在串的開頭或者結尾。

2. 除了上文提到的元字符外， POSIX正則表達式還支持下列模式匹配元字符。

   元字符	含義
   ^	表示串開頭的匹配
   $	表示串末尾的匹配
   .	匹配任意單個字符

示例：

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

雖然大部分的正則表達式搜索都能很快地執(zhí)行，但是正則表達式仍可能被人為地弄成需要任意長的時間和任意量的內存進行處理。不建議從非安全模式來源接受正則表達式搜索模式，如果必須這樣做，建議加上語句超時限制。使用SIMILAR TO模式的搜索具有同樣的安全性危險， 因為SIMILAR TO提供了很多和POSIX-風格正則表達式相同的能力。LIKE搜索比其他兩種選項簡單得多，因此在接受非安全模式來源搜索時要更安全些。

👍 點贊，你的認可是我創(chuàng)作的動力！

?? 收藏，你的青睞是我努力的方向！