SQL Function – REGEXP_REPLACE & REGEXP_SUBSTR

adminLinux, MariaDB, Mysql, RedHat / CentOS

Loading

測試環境為 CentOS 8 x86_64 (虛擬機) 

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.17-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

REGEXP_REPLACE

想要把某字串內容取代成為另一個字串可以透過 函數 REPLACE .但是很可惜的是無法搭配 REGEXP (正規表示式) 來使用.這時候可以使用另外一個函數 REGEXP_REPLACE 來滿足.

正規表示式字元可以使用下面的的特殊符號來表示:

  • ^ 表示匹配的字元串在行首.
  • $ 表示匹配的字元串在行尾.
    下面範例用來剔除 字串前後的空白字元.

    MariaDB [(none)]> SELECT REGEXP_REPLACE('    This is a book   ' , '^\\s+ | \\s+$' ,'') AS T1;
+----------------+
| T1             |
+----------------+
| This is a book |
+----------------+
1 row in set (0.000 sec)
  • . 表示為任一字元.
  • * 表示 “重複” 零個到無窮多個的字元.
  • + 表示 “重複” 一個到無窮多個的字元.
    下面範例用來剔除 HTML 語法的標籤(TAG).

    MariaDB [(none)]> SELECT REGEXP_REPLACE("<h2 title=I'm a header>The title Attribute</h2>","<.+>",'');
+-----------------------------------------------------------------------------+
| REGEXP_REPLACE("<h2 title=I'm a header>The title Attribute</h2>","<.+>",'') |
+-----------------------------------------------------------------------------+
|                                                                             |
+-----------------------------------------------------------------------------+
1 row in set (0.000 sec)

    .+
    . 表示為任一字元 , + 表示 “重複” 一個到無窮多個的字元.

  • ? 表示 “重複” 零個到一個的字元.
    下面範例用來剔除 HTML 語法的標籤(TAG).

    MariaDB [(none)]> SELECT REGEXP_REPLACE("<h2 title=I'm a header>The title Attribute</h2>","<.+?>",'');
+------------------------------------------------------------------------------+
| REGEXP_REPLACE("<h2 title=I'm a header>The title Attribute</h2>","<.+?>",'') |
+------------------------------------------------------------------------------+
| The title Attribute                                                          |
+------------------------------------------------------------------------------+
1 row in set (0.004 sec)

    .+?
    . 表示為任一字元. + 表示 “重複” 一個到無窮多個的字元 , ? 表示 “重複” 零個到一個的字元.

    .+ 與 .+? 使用上不同.

    • .+ 找出盡可能多的字元,上面的範例.
      <.+> 會找出盡可能多的字元 以 < 為開頭 ,以 > 為結尾 ,結果就是找到 <h2 title=I’m a header>The title Attribute</h2> 一整個字串.
    • .+? 找出盡可能少的字元,上面的範例.
      <.+?> 會找出盡可能少的字元 以 < 為開頭 ,以 > 為結尾 ,結果就是找到 <h2 title=I’m a header>與 </h2> 兩個字串.
  • A|B , (A|B) 可以是 A 或是 B.
    之前要取代多個字串都需要寫成多個 REPLACE , 透過 | 就可以一次取代多個指定字串, 以範例 A|B|c 來看,表示 A , B 與 C 都會被取代.

    MariaDB [(none)]> SELECT REGEXP_REPLACE("ABCDEF","A|C|B",'');
+-------------------------------------+
| REGEXP_REPLACE("ABCDEF","A|C|B",'') |
+-------------------------------------+
| DEF                                 |
+-------------------------------------+
1 row in set (0.000 sec)
  • {n} 配對次數 n 次(須為整數).
  • [ ] 包含 [ ] 內的任一字元,可以使用 [0-9] 代表從數字 0-9 , 或是 [a-z].
  • {n,m} 配對次數從 n 到 m 次(皆須為整數,n 需小於等於 m , n<=m).
  • [^ ] 不包含 [ ] 內的任一字元.
    以下範例用來取代掉非數字(只保留數字).

    MariaDB [astl1]> SELECT REGEXP_REPLACE('0988-123456', '[^0-9]+', '');
+----------------------------------------------+
| REGEXP_REPLACE('0988-123456', '[^0-9]+', '') |
+----------------------------------------------+
| 0988123456                                   |
+----------------------------------------------+
1 row in set (0.000 sec)

    以下範例用來取代掉非數字與.小點數 (只保留數字與小數點).

    MariaDB [(none)]> SELECT REGEXP_REPLACE('PI=3.1415', '[^0-9 , .]+', '');
+------------------------------------------------+
| REGEXP_REPLACE('PI=3.1415', '[^0-9 , .]+', '') |
+------------------------------------------------+
| 3.1415                                         |
+------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> SELECT REGEXP_REPLACE('PI=3.1415', '[^0-9 , ^.]+', '');
+-------------------------------------------------+
| REGEXP_REPLACE('PI=3.1415', '[^0-9 , ^.]+', '') |
+-------------------------------------------------+
| 3.1415                                          |
+-------------------------------------------------+
1 row in set (0.000 sec)
  • \d (實際需使用 \\d) 符合一個數字(digit) 字元.
  • \D (實際需使用 \\D) 符合一個非數字(non-digit) 字元.
  • \w (實際需使用 \\w) 符合一個英文(word) 字元.
  • \W (實際需使用 \\W) 符合一個非英文(non-word) 字元.
  • \s (實際需使用 \\s) 符合一個空白(white-space) 字元.
  • \S (實際需使用 \\S) 符合一個非空白(non-white-space) 字元.
  • () 表示將字串(表達式 : sub-expression) 分組為一個子字串 (子表達式 : sub-expression),搭配 \N 來使用.
  • \N (實際需使用 \\N) N 須為 1~9 間的數字,代表在字串中找到的第 N個匹配的子字串 (sub-expression).
    下面範例用來將字串(名 姓)轉換成為(姓, 名).

    MariaDB [(none)]> SELECT REGEXP_REPLACE('Chris Hemsworth','^(.*) (.*)$','\\2, \\1');
+------------------------------------------------------------+
| REGEXP_REPLACE('Chris Hemsworth','^(.*) (.*)$','\\2, \\1') |
+------------------------------------------------------------+
| Hemsworth, Chris                                           |
+------------------------------------------------------------+
1 row in set (0.000 sec)

    ^(.*) (.*)$ 與 \\2, \\1

    • ^ 表示匹配的字元串在行首.
    • $ 表示匹配的字元串在行尾.
    • . 表示為任一字元.
    • * 表示 “重複” 零個到無窮多個的字元.
      .* 表示為任意字元.
    • () 表示將字串(表達式 : sub-expression) 分組為一個子字串 (子表達式 : sub-expression),搭配 \N 來使用.
    • \N (實際需使用 \\N) N 須為 1~9 間的數字,代表在字串中找到的第 N個匹配的子字串 (sub-expression).

如果要取代的資料是以上關鍵字,可以使用 \\+ 來表示 取代 +(加號).

(?i) 與 (?-i) PCRE flags.

為增強正規表示式的性能 MariaDB 10.0.5 開始支援 PCRE (Perl Compatible Regular Expressions),更多關於 (?i) 與 (?-i) 請參考 – https://benjr.tw/102769 .

  • (?i) 強制不區分大小寫.
    預設是不區分大小寫的.

    MariaDB [(none)]> SELECT REGEXP_REPLACE('ABC','b','-');
+-------------------------------+
| REGEXP_REPLACE('ABC','b','-') |
+-------------------------------+
| A-C                           |
+-------------------------------+
1 row in set (0.001 sec)

    當你指定 有區分大小寫 ( Case sensitive ) 的 文字排序 ( Collations ),或是指定為 Binary 的資料格式 (Data Type) ,才會是有區分大小寫.

    MariaDB [(none)]> SELECT REGEXP_REPLACE(BINARY 'ABC','b','-') ;
+--------------------------------------+
| REGEXP_REPLACE(BINARY 'ABC','b','-') |
+--------------------------------------+
| ABC                                  |
+--------------------------------------+
1 row in set (0.001 sec)

    MariaDB [(none)]> SELECT REGEXP_REPLACE('ABC' COLLATE latin1_bin,'b','-');
+--------------------------------------------------+
| REGEXP_REPLACE('ABC' COLLATE latin1_bin,'b','-') |
+--------------------------------------------------+
| ABC                                              |
+--------------------------------------------------+
1 row in set (0.000 sec)

    這時候可以使用強制不區分大小寫.

    MariaDB [(none)]> SELECT REGEXP_REPLACE(BINARY 'ABC','(?i)b','-');
+------------------------------------------+
| REGEXP_REPLACE(BINARY 'ABC','(?i)b','-') |
+------------------------------------------+
| A-C                                      |
+------------------------------------------+
1 row in set (0.001 sec)
  • (?-i) 強制區分大小寫. 
    MariaDB [(none)]> SELECT REGEXP_REPLACE(BINARY 'ABC','(?-i)b','-');
+-------------------------------------------+
| REGEXP_REPLACE(BINARY 'ABC','(?-i)b','-') |
+-------------------------------------------+
| ABC                                       |
+-------------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> SELECT REGEXP_REPLACE(BINARY 'ABC','(?-i)B','-');
+-------------------------------------------+
| REGEXP_REPLACE(BINARY 'ABC','(?-i)B','-') |
+-------------------------------------------+
| A-C                                       |
+-------------------------------------------+
1 row in set (0.000 sec)

REGEXP_SUBSTR

REGEXP_SUBSTR 使用上跟 REGEXP_REPLACE 類似,都是透過標準表示式來處理資料,不過 REGEXP_SUBSTR 是依據標準表示式來抓資料,以前面範例來看.

MariaDB [(none)]> SELECT REGEXP_SUBSTR("<h2 title=I'm a header>The title Attribute</h2>","<.+>");
+-------------------------------------------------------------------------+
| REGEXP_SUBSTR("<h2 title=I'm a header>The title Attribute</h2>","<.+>") |
+-------------------------------------------------------------------------+
| <h2 title=I'm a header>The title Attribute</h2>                         |
+-------------------------------------------------------------------------+

MariaDB [(none)]> SELECT REGEXP_SUBSTR("<h2 title=I'm a header>The title Attribute</h2>","<.+?>");
+--------------------------------------------------------------------------+
| REGEXP_SUBSTR("<h2 title=I'm a header>The title Attribute</h2>","<.+?>") |
+--------------------------------------------------------------------------+
| <h2 title=I'm a header>                                                  |
+--------------------------------------------------------------------------+
沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步了解 Akismet 如何處理網站訪客的留言資料。