PostgreSQL模式匹配與正則表達式方法總結
1.1 PostgreSQL中的模式匹配和正則表達式
PostgreSQL提供了以下3種實現(xiàn)模式匹配的方法：
- ·傳統(tǒng)SQL的LIKE操作符。
- ·SQL99標準新增的SIMILAR TO操作符。
- ·POSIX風格的正則表達式。
另外，還有一個模式匹配函數(shù)substring可用，它可以使用SIMILAR TO風格或者POSIX風格的正則表達式。后面會詳細介紹substring函數(shù)的使用方法。
1.2 傳統(tǒng)SQL的LIKE操作符
傳統(tǒng)的LIKE操作符比較簡單，其中百分號“%”代表0個或任意個字符，而下劃線“_”代表任意一個字符，示例如下：
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)
如果想匹配字符串中的百分號“%”自身或下劃線“_”自身怎么辦呢？可以在字符串前加轉(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)義字符也可以通過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)義字符本身可以通過使用連續(xù)兩個轉(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還提供了標準SQL中沒有的ILIKE操作符，用于忽略大小寫的模式匹配。
PostgreSQL還提供了如下與LIKE等價的操作符。
·~~：等效于LIKE。
·~~*：等效于ILIKE。
·!~~：等效于NOT LIKE。
·!~~*：操作符NOT ILIKE。
1.3 SIMILAR TO正則表達式
SIMILAR TO是SQL99標準定義的正則表達式。SQL標準的正則表達式是混合了LIKE和普通的正則表達式的一個雜合體。
SIMILAR TO操作符只有匹配整個字符串時才能匹配成功，這一點與LIKE相同，而與普通的正則表達式只匹配部分的習慣不同。SIMILAR TO與LIKE一樣也使用下劃線和百分號分別匹配單個字符和任意字符串。
除了從LIKE借用的這些功能之外，SIMILAR TO還支持以下幾個與POSIX正則表達式相同的模式匹配元字符。
·|：表示選擇兩個候選項之一。
·*：表示重復前面的項0次或更多次。
·+：表示重復前面的項一次或更多次。
·?：表示重復前面的項0次或一次。
·{m}：表示重復前面的項m次。
·{m，}：表示重復前面的項m次或更多次。
·{m，n}：表示重復前面的項至少m次，不超過n次。
·括號()：可以作為項目分組到一個獨立的邏輯項。
·[...]：聲明一個字符類，就像POSIX正則表達式。
在SIMILAR TO中英文的句號“.”并不是元字符。和LIKE操作符一樣，可以使用反斜杠關閉這些元字符所有的特殊含義，當然也可以用ESCAPE聲明另外一個轉(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正則表達式
POSIX正則表達式的模式匹配操作符有以下幾個。
·~：匹配正則表達式，區(qū)分大小寫。
·~*：匹配正則表達式，不區(qū)分大小寫。
·!~：不匹配正則表達式，區(qū)分大小寫。
·!~*：不匹配正則表達式，不區(qū)分大小寫。
POSIX正則表達式提供了比LIKE和SIMILAR TO操作符更強大的模式匹配方法。許多UNIX的命令如egrep、sed、awk都使用類似的模式匹配語言。
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正則表達式中，百分號與下劃線沒有像LIKE或SIMILAR TO操作符中的特殊意義，示例如下：
maxwelldb=# select 'osdba' ~ '%'; ?column? ---------- f (1 row) maxwelldb=# select 'osdba' ~ '_sdba'; ?column? ---------- f (1 row) maxwelldb=#
在POSIX正則表達式中與SIMILAR TO和LIKE操作符不一樣的是，只要部分匹配到字符串就返回真，這與UNIX中的grep命令是一樣的。
maxwelldb=# select 'osdba' ~ 'os'; ?column? ---------- t (1 row) maxwelldb=#
如果想匹配開頭或結尾，需要使用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中有一個很強大的函數(shù)substring，該函數(shù)可以使用正則表達式。substring有以下3種用法。
第一種：substring（<字符串>,<數(shù)字>,[數(shù)字]）。
后兩個參數(shù)為數(shù)字，該函數(shù)和其他語言中的substr函數(shù)的含義相同，示例如下。
maxwelldb=# select substring('osdba',2);
substring
-----------
sdba
(1 row)
maxwelldb=# 第二種：substring（<字符串>,<字符串>）。
有兩個參數(shù)且都是字符串，這是一種使用POSIX正則表達式的方式。
前面說過，在PostgreSQL中有兩種正則表達式，一種被稱為SQL正則表達式；另一種被稱為POSIX正則表達式。POSIX正則表達式就是一般在腳本語言中使用的標準正則表達式，而SQL正則表達式首先是遵循SQL語句中的LIKE語法的，如字符“.”在POSIX正則表達式中代表任意字符，而在SQL表達式中就只能表示自己，表示任意字符的元字符是“_”。
SQL正則表達式中“%”可以表示任意個字符，而這在POSIX正則表達式中要用“.*”來表示。
同時SQL正則表達式也支持以下語法。
·|：表示選擇（兩個候選之一），這在POSIX正則表達式中不支持。
·*：表示重復前面的項0次或更多次。
·+：表示重復前面的項一次或更多次。
·()：把項組合成一個邏輯項。
·[...]：聲明一個字符類。
SIMILAR TO中使用的就是SQL正則表達式，而“~”使用的是POSIX正則表達式，注意兩者間的如下區(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正則表達式要求全部匹配才為真，而POSIX表達式中只要包含就為真。
只有兩個參數(shù)的substring中的正則表達式是POSIX正則表達式，而不是SQL正則表達式，示例如下：
maxwelldb=# select substring('osdba-5-osdba',E'(\\d+)');
substring
-----------
5
(1 row)
maxwelldb=# 這種方式的substring函數(shù)返回正則表達式中“()”中匹配的部分。
第三種：substring（<字符串>,<字符串>,<字符串）或substring（<字符串> from <字符串> for <字符串）。
這種形式的substring使用SQL正則表達式，第三個參數(shù)為指定一個轉(zhuǎn)義字符。示例如下：
maxwelldb=# select substring('osdba-5-osdba','%#"[0-9]+#"%','#');
substring
-----------
5
(1 row)
maxwelldb=# 總結
到此這篇關于PostgreSQL模式匹配與正則表達式的文章就介紹到這了,更多相關PostgreSQL模式匹配與正則內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
這篇文章主要介紹了postgresql之使用lsn 獲取 wal文件名的實例，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
解決postgreSql 將Varchar類型字段修改為Int類型報錯的問題
這篇文章主要介紹了解決postgreSql 將Varchar類型字段修改為Int類型報錯的問題，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12
這篇文章主要介紹了postgresql查看表和索引的情況,判斷是否膨脹的操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
postgresql 數(shù)據(jù)庫 與TimescaleDB 時序庫 join 在一起
這篇文章主要介紹了postgresql 數(shù)據(jù)庫 與TimescaleDB 時序庫 join 在一起,需要的朋友可以參考下2020-12-12
PostgreSQL之分區(qū)表(partitioning)
通過合理的設計，可以將選擇一定的規(guī)則，將大表切分多個不重不漏的子表，這就是傳說中的partitioning。比如，我們可以按時間切分，每天一張子表，比如我們可以按照某其他字段分割，總之了就是化整為零，提高查詢的效能2016-11-11
這篇文章主要介紹了pgsql 實現(xiàn)分頁查詢方式，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
這篇文章主要介紹了解決postgresql 序列跳值的問題，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-02-02
Windows?系統(tǒng)?PostgreSQL?手工安裝配置方法
這篇文章主要介紹了Windows?系統(tǒng)?PostgreSQL?手工安裝配置方法,本文主要說一下在?Windows?系統(tǒng)中安裝?PostgreSQL?的方法，我這里沒有采用?exe?安裝包的形式去安裝，EDB?發(fā)布的那個?exe?安裝包形式的對于中文環(huán)境數(shù)據(jù)庫的排序規(guī)則設定有問題，需要的朋友可以參考下2022-09-09
這篇文章主要介紹了PostgreSQL教程(八)：索引詳解,本文講解了索引的類型、復合索引、組合多個索引、唯一索引、表達式索引、部分索引等內(nèi)容,需要的朋友可以參考下2015-05-05
基于postgresql數(shù)據(jù)庫鎖表問題的解決
這篇文章主要介紹了基于postgresql數(shù)據(jù)庫鎖表問題的解決，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12