1.1 PostgreSQL中的模式匹配和正則表達(dá)式

PostgreSQL提供了以下3種實(shí)現(xiàn)模式匹配的方法：

• ·傳統(tǒng)SQL的LIKE操作符。
• ·SQL99標(biāo)準(zhǔn)新增的SIMILAR TO操作符。
• ·POSIX風(fēng)格的正則表達(dá)式。

另外，還有一個(gè)模式匹配函數(shù)substring可用，它可以使用SIMILAR TO風(fēng)格或者POSIX風(fēng)格的正則表達(dá)式。后面會(huì)詳細(xì)介紹substring函數(shù)的使用方法。

1.2 傳統(tǒng)SQL的LIKE操作符

傳統(tǒng)的LIKE操作符比較簡(jiǎn)單，其中百分號(hào)“%”代表0個(gè)或任意個(gè)字符，而下劃線“_”代表任意一個(gè)字符，示例如下：

maxwelldb=# select * from mytab3;
 id |   note   
----+----------
  1 | abcabefg
  2 | abxyz
  3 | 123abc
  4 | ab_abefg
  5 | ab%abefg
  6 | ababefg
(6 rows)
 
maxwelldb=# select * from mytab3 where note like 'ab_ab%';
 id |   note   
----+----------
  1 | abcabefg
  4 | ab_abefg
  5 | ab%abefg
(3 rows)
 
maxwelldb=# select * from mytab3 where note like 'ab%ab%';
 id |   note   
----+----------
  1 | abcabefg
  4 | ab_abefg
  5 | ab%abefg
  6 | ababefg
(4 rows)

如果想匹配字符串中的百分號(hào)“%”自身或下劃線“_”自身怎么辦呢？可以在字符串前加轉(zhuǎn)義字符反斜杠“\”，示例如下：

maxwelldb=# select * from mytab3 where note like '%\%%';
 id |   note   
----+----------
  5 | ab%abefg
(1 row)
 
maxwelldb=# select * from mytab3 where note like '%\_%';
 id |   note   
----+----------
  4 | ab_abefg
(1 row)

轉(zhuǎn)義字符也可以通過(guò)ESCAPE子句指定為其他字符，如指定成“#”，命令如下：

maxwelldb=# select * from mytab3 where note like '%#%%' escape '#';
 id |   note   
----+----------
  5 | ab%abefg
(1 row)
 
maxwelldb=# select * from mytab3 where note like '%#_%' escape '#';
 id |   note   
----+----------
  4 | ab_abefg
(1 row)
 
maxwelldb=# 

轉(zhuǎn)義字符本身可以通過(guò)使用連續(xù)兩個(gè)轉(zhuǎn)義字符去除轉(zhuǎn)義字符的特殊意義，示例如下：

maxwelldb=# insert into mytab3 value3(7,'\');
ERROR:  syntax error at or near "value3"
LINE 1: insert into mytab3 value3(7,'\');
                           ^
maxwelldb=# insert into mytab3 values(7,'\');
INSERT 0 1
maxwelldb=# insert into mytab3 values(8,'#');
INSERT 0 1
maxwelldb=# select * from mytab3 where note like '%\\%';
 id | note 
----+------
  7 | \
(1 row)
 
maxwelldb=# select * from mytab3 where note like '%##%' escape '#';
 id | note 
----+------
  8 | #
(1 row)

PostgreSQL還提供了標(biāo)準(zhǔn)SQL中沒(méi)有的ILIKE操作符，用于忽略大小寫(xiě)的模式匹配。

PostgreSQL還提供了如下與LIKE等價(jià)的操作符。

·~~：等效于LIKE。

·~~*：等效于ILIKE。

·!~~：等效于NOT LIKE。

·!~~*：操作符NOT ILIKE。

1.3 SIMILAR TO正則表達(dá)式

SIMILAR TO是SQL99標(biāo)準(zhǔn)定義的正則表達(dá)式。SQL標(biāo)準(zhǔn)的正則表達(dá)式是混合了LIKE和普通的正則表達(dá)式的一個(gè)雜合體。

SIMILAR TO操作符只有匹配整個(gè)字符串時(shí)才能匹配成功，這一點(diǎn)與LIKE相同，而與普通的正則表達(dá)式只匹配部分的習(xí)慣不同。SIMILAR TO與LIKE一樣也使用下劃線和百分號(hào)分別匹配單個(gè)字符和任意字符串。

除了從LIKE借用的這些功能之外，SIMILAR TO還支持以下幾個(gè)與POSIX正則表達(dá)式相同的模式匹配元字符。

·|：表示選擇兩個(gè)候選項(xiàng)之一。

·*：表示重復(fù)前面的項(xiàng)0次或更多次。

·+：表示重復(fù)前面的項(xiàng)一次或更多次。

·?：表示重復(fù)前面的項(xiàng)0次或一次。

·{m}：表示重復(fù)前面的項(xiàng)m次。

·{m，}：表示重復(fù)前面的項(xiàng)m次或更多次。

·{m，n}：表示重復(fù)前面的項(xiàng)至少m次，不超過(guò)n次。

·括號(hào)()：可以作為項(xiàng)目分組到一個(gè)獨(dú)立的邏輯項(xiàng)。

·[...]：聲明一個(gè)字符類，就像POSIX正則表達(dá)式。

在SIMILAR TO中英文的句號(hào)“.”并不是元字符。和LIKE操作符一樣，可以使用反斜杠關(guān)閉這些元字符所有的特殊含義，當(dāng)然也可以用ESCAPE聲明另外一個(gè)轉(zhuǎn)義字符。

maxwelldb=# select 'osdba' SIMILAR TO 'a';
 ?column? 
----------
 f
(1 row)
 
maxwelldb=# select 'osdba' SIMILAR TO '%(b|a)';
 ?column? 
----------
 t
(1 row)
 
maxwelldb=# select 'osdb' SIMILAR TO '%(s|d)%';
 ?column? 
----------
 t
(1 row)
 
maxwelldb=# 

1.4 POSIX正則表達(dá)式

POSIX正則表達(dá)式的模式匹配操作符有以下幾個(gè)。

·~：匹配正則表達(dá)式，區(qū)分大小寫(xiě)。

·~*：匹配正則表達(dá)式，不區(qū)分大小寫(xiě)。

·!~：不匹配正則表達(dá)式，區(qū)分大小寫(xiě)。

·!~*：不匹配正則表達(dá)式，不區(qū)分大小寫(xiě)。

POSIX正則表達(dá)式提供了比LIKE和SIMILAR TO操作符更強(qiáng)大的模式匹配方法。許多UNIX的命令如egrep、sed、awk都使用類似的模式匹配語(yǔ)言。

maxwelldb=# 
maxwelldb=# select 'osdba' ~ 'a';
 ?column? 
----------
 t
(1 row)
 
maxwelldb=# select 'osdba' ~ '(b|a)*';
 ?column? 
----------
 t
(1 row)
 
maxwelldb=# select 'osdb' ~ '.*(b|a).*';
 ?column? 
----------
 t
(1 row)
 
maxwelldb=# select 'osdba' ~ '(s|d).*';
 ?column? 
----------
 t
(1 row)
 
maxwelldb=# 

在POSIX正則表達(dá)式中，百分號(hào)與下劃線沒(méi)有像LIKE或SIMILAR TO操作符中的特殊意義，示例如下：

 maxwelldb=# select 'osdba' ~ '%';
 ?column? 
----------
 f
(1 row)
 
maxwelldb=# select 'osdba' ~ '_sdba';
 ?column? 
----------
 f
(1 row)
 
maxwelldb=# 

在POSIX正則表達(dá)式中與SIMILAR TO和LIKE操作符不一樣的是，只要部分匹配到字符串就返回真，這與UNIX中的grep命令是一樣的。

maxwelldb=# select 'osdba' ~ 'os';
 ?column? 
----------
 t
(1 row)
 
maxwelldb=# 

如果想匹配開(kāi)頭或結(jié)尾，需要使用POSIX中的“^”或“$”元字符，示例如下：

maxwelldb=# select 'aosdba' ~ 'os';
 ?column? 
----------
 t
(1 row)
 
maxwelldb=# select 'aosdba' ~ '^os';
 ?column? 
----------
 f
(1 row)
 
maxwelldb=# select 'osdba' ~ 'db';
 ?column? 
----------
 t
(1 row)
 
maxwelldb=# select 'osdba' ~ 'db$';
 ?column? 
----------
 f
(1 row)
 
maxwelldb=# select 'osdba' ~ 'dba$';
 ?column? 
----------
 t
(1 row)
 
maxwelldb=# 

1.5 模式匹配函數(shù)substring

PostgreSQL中有一個(gè)很強(qiáng)大的函數(shù)substring，該函數(shù)可以使用正則表達(dá)式。substring有以下3種用法。

第一種：substring（<字符串>,<數(shù)字>,[數(shù)字]）。

后兩個(gè)參數(shù)為數(shù)字，該函數(shù)和其他語(yǔ)言中的substr函數(shù)的含義相同，示例如下。

maxwelldb=# select substring('osdba',2);
 substring 
-----------
 sdba
(1 row)
 
maxwelldb=# 

第二種：substring（<字符串>,<字符串>）。

有兩個(gè)參數(shù)且都是字符串，這是一種使用POSIX正則表達(dá)式的方式。

前面說(shuō)過(guò)，在PostgreSQL中有兩種正則表達(dá)式，一種被稱為SQL正則表達(dá)式；另一種被稱為POSIX正則表達(dá)式。POSIX正則表達(dá)式就是一般在腳本語(yǔ)言中使用的標(biāo)準(zhǔn)正則表達(dá)式，而SQL正則表達(dá)式首先是遵循SQL語(yǔ)句中的LIKE語(yǔ)法的，如字符“.”在POSIX正則表達(dá)式中代表任意字符，而在SQL表達(dá)式中就只能表示自己，表示任意字符的元字符是“_”。

SQL正則表達(dá)式中“%”可以表示任意個(gè)字符，而這在POSIX正則表達(dá)式中要用“.*”來(lái)表示。

同時(shí)SQL正則表達(dá)式也支持以下語(yǔ)法。

·|：表示選擇（兩個(gè)候選之一），這在POSIX正則表達(dá)式中不支持。

·*：表示重復(fù)前面的項(xiàng)0次或更多次。

·+：表示重復(fù)前面的項(xiàng)一次或更多次。

·()：把項(xiàng)組合成一個(gè)邏輯項(xiàng)。

·[...]：聲明一個(gè)字符類。

SIMILAR TO中使用的就是SQL正則表達(dá)式，而“~”使用的是POSIX正則表達(dá)式，注意兩者間的如下區(qū)別：

maxwelldb=# select 'osdba' ~ 'sdb';
 ?column? 
----------
 t
(1 row)
 
maxwelldb=# select 'osdba' similar to 'sdb';
 ?column? 
----------
 f
(1 row)
 
maxwelldb=# select 'osdba' similar to '%sdb%';
 ?column? 
----------
 t
(1 row)
 
maxwelldb=# 

從上面的示例中可以看出，SQL正則表達(dá)式要求全部匹配才為真，而POSIX表達(dá)式中只要包含就為真。

只有兩個(gè)參數(shù)的substring中的正則表達(dá)式是POSIX正則表達(dá)式，而不是SQL正則表達(dá)式，示例如下：

maxwelldb=# select substring('osdba-5-osdba',E'(\\d+)');
 substring 
-----------
 5
(1 row)
 
maxwelldb=# 

這種方式的substring函數(shù)返回正則表達(dá)式中“()”中匹配的部分。

第三種：substring（<字符串>,<字符串>,<字符串）或substring（<字符串> from <字符串> for <字符串）。

這種形式的substring使用SQL正則表達(dá)式，第三個(gè)參數(shù)為指定一個(gè)轉(zhuǎn)義字符。示例如下：

maxwelldb=# select substring('osdba-5-osdba','%#"[0-9]+#"%','#');
 substring 
-----------
 5
(1 row)
 
maxwelldb=# 

總結(jié)

到此這篇關(guān)于PostgreSQL模式匹配與正則表達(dá)式的文章就介紹到這了,更多相關(guān)PostgreSQL模式匹配與正則內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！