SQL Function – REGEXP_REPLACE

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

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

  • ^ 表示匹配的字元串在行首.
  • $ 表示匹配的字元串在行尾.
  • . 表示為任一字元.
  • * 表示 “重複” 零個到無窮多個的字元.
  • + 表示 “重複” 一個到無窮多個的字元.
  • ? 表示 “重複” 零個到一個的字元.
  • A|B , (A|B) 可以是 A 或是 B.
  • {n} 配對次數 n 次(須為整數).
  • [ ] 包含 [ ] 內的任一字元.
  • {n,m} 配對次數從 n 到 m 次(皆須為整數,n 需小於等於 m , n<=m).
  • [^ ] 不包含 [ ] 內的任一字元.
  • \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).

以上都可以組合來使用,來看一下下面的範例.

測試環境為 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.

|

  • A|B 表示 A 與 B 都會被取代.
MariaDB [(none)]> SELECT REGEXP_REPLACE("ABCDEF","A|C|B",'');
+-------------------------------------+
| REGEXP_REPLACE("ABCDEF","A|C|B",'') |
+-------------------------------------+
| DEF                                 |
+-------------------------------------+
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)

.+

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

下面範例用來剔除 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)

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

  • .+ 找出盡可能多的字元,上面的範例.
    <.+> 會找出盡可能多的字元 以 < 為開頭 ,以 > 為結尾 ,結果就是找到 <h2 title=I’m a header>The title Attribute</h2> 一整個字串.
  • .+? 找出盡可能少的字元,上面的範例.
    <.+?> 會找出盡可能少的字元 以 < 為開頭 ,以 > 為結尾 ,結果就是找到 <h2 title=I’m a header>與 </h2> 兩個字串.

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

  • ^ 表示匹配的字元串在行首.
  • $ 表示匹配的字元串在行尾.
  • . 表示為任一字元.
  • * 表示 “重複” 零個到無窮多個的字元.
    .* 表示為任意字元.
  • () 表示將字串(表達式 : 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)

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

為增強正規表示式的性能 MariaDB 10.0.5 開始支援 PCRE (Perl Compatible Regular Expressions),更多關於 (?i) 與 (?-i) 請參考 – http://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)
    
沒有解決問題,試試搜尋本站其他內容

發佈留言

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

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