PostgreSQL字元切割:substring函數的用法說明

2021-02-01 12:08:43

作為當前最強大的開源資料庫，Postgresql（以下簡稱pg）對字元的處理也是最為強大的，首先他也有substr，trim等其他資料庫都有的普通函數，這裡我們介紹他更強大的一個函數substring，可以像python，java等程式語言一樣使用正規表示式，強大到極點

在此之前我們先了解一下正規表示式最基礎的四個

%代表一個或多個字元 _代表一個字元 ^代表字元前 $代表字元后

pg的官網上對其用法是下面這樣，但不夠清楚瞭然，下面我一一解釋

用法和substr差不多，是指定序列，from 2 for 3 是從序列為2的位置開始取3個字元。例子如上

例子用法的意思是：$代表字元后, 一個點代表一個字元，即從最末尾開始選擇3個字元，同樣的如果是substring('Thomas' from '^....') 則結果是Thom

用法是最實用的，也是最難理解的，先大致理解：

from '%#"o_a#"_' for '#' 中from是開始(包含),for後面跟的是逃逸，即結束

這個例子的意思是我想要o_a的組合字元，其中o_a的多個(%)字元不要，後面的一個(_)字元不要，這裡要注意的是切分後面剩多少字元寫幾個_

這裡的架構可以理解為固定的，即 from '#"#"' for '#' #" 是分割字元，可以在#"前中後限定選擇你最想要的字元

下面用範例來講解一下第三個用法

需求：下圖的查詢結果是查的紀錄檔表,我想要【】裡的資料，由於【】裡字元長度不固定，又只能用sql來切割，因此只能使用第三種方法來獲取【】裡的資料

根據上面的思想，我可以寫這樣的架構'【#"%#"_______' FOR '#'，試著執行一下

SELECT
 SUBSTRING (
 log_txt
 FROM
  '【#"%#"_______' FOR '#'
 ) log_txt
FROM
 operation_log
WHERE
 log_type = '15'
LIMIT 3

也可以根據position這個函數來解決，這個函數類似於python的index，就是把字串的某個字元轉變為該字元所在的位置數，如此一來便可以使用substring的第一個例子用法，即

SUBSTRING ( log_txt FROM 2 FOR position('【'))

ooook 搞定了！！！

補充：Postgresql之split_part()切割函數

如下所示：

split_part(string text, delimiter text2, field int)

text要切割的欄位； text2按照什麼形式切割 int擷取的位置

ps:

text=「name.cn」 split_part(text,'.',1) 結果： name
text=「name.cn」 split_part(text,'.',2) 結果： cn
text=「name.cn.com」 split_part(text,'.',3) 結果： com

以上為個人經驗，希望能給大家一個參考，也希望大家多多支援it145.com。如有錯誤或未考慮完全的地方，望不吝賜教。