MariaDB – 常用處理字串函數 ( String Functions )

測試環境為 CentOS 8 x86_64 虛擬機.

  1. 字串函數 ( String Functions ) – 這邊介紹.
  2. 數字函數 (Numeric Functions)http://benjr.tw/101970
  3. 日期與時間函數 (Date & Time Functions)http://benjr.tw/102964
  4. 資訊函數 (Information Functions)http://benjr.tw/102961
  5. 搭配 GROUP BY 函數 (Aggregate Functions)http://benjr.tw/102967
  6. 流程控制函數 (Control Flow Functions)http://benjr.tw/102747

先建立一個測試用資料庫 (testdb) , 與 tables (employee) ,下面範例會使用到 . 格式為 K1 int(11) – auto_increment & PRIMARY KEY , Name char(20), Dept char(20), jobTitle char(20) ,email char(30) 以及 Salary int(11).

SELECT 查詢需注意資料庫字元編碼 Character Sets 與 文字排序 Collations ,請參考資料庫編碼 – http://benjr.tw/102156 .

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.3.11-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.

MariaDB [(none)]> CREATE DATABASE testdb DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> USE testdb;
Database changed
MariaDB [testdb]> CREATE TABLE employee (K1 int(11) NOT NULL auto_increment, Name char(20) NOT NULL, Dept char(20) NOT NULL, JobTitle char(20) NOT NULL, Salary int(11) NOT NULL , email char(30) NOT NULL, PRIMARY KEY (K1)) ;
Query OK, 0 rows affected (0.010 sec)
 
MariaDB [testdb]> DESCRIBE employee; 
+----------+----------+------+-----+---------+----------------+
| Field    | Type     | Null | Key | Default | Extra          |
+----------+----------+------+-----+---------+----------------+
| K1       | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name     | char(20) | NO   |     | NULL    |                |
| Dept     | char(20) | NO   |     | NULL    |                |
| JobTitle | char(20) | NO   |     | NULL    |                |
| Salary   | int(11)  | NO   |     | NULL    |                |
| email    | char(30) | NO   |     | NULL    |                |
+----------+----------+------+-----+---------+----------------+
6 rows in set (0.004 sec)

透過 INSERT 新增加了五筆資料.

MariaDB [testdb]> INSERT INTO employee (Name , Dept , JobTitle , Salary , email) VALUES ('Ben' , 'Testing' , 'Engineer' , '45000' , 'ben@benjr.tw') , ('Afa' , 'Power' , 'Engineer' , '48000' , 'Afa@benjr.tw') , ('Boss' ,'Testing' , 'Manager' , '75000' , 'Boss@benjr.tw') , ('JoJo' , 'Testing' , 'Engineer' , '45000' , 'jojo@benjr.tw') ,  ('Ian' , 'PM' , 'Engineer' , '85000' , 'ian@benjr.tw');
Query OK, 5 rows affected (0.002 sec)
Records: 5  Duplicates: 0  Warnings: 0
 
MariaDB [testdb]> SELECT * FROM employee;
+----+------+---------+----------+--------+---------------+
| K1 | Name | Dept    | JobTitle | Salary | email         |
+----+------+---------+----------+--------+---------------+
|  1 | Ben  | Testing | Engineer |  45000 | ben@benjr.tw  |
|  2 | Afa  | Power   | Engineer |  48000 | Afa@benjr.tw  |
|  3 | Boss | Testing | Manager  |  75000 | Boss@benjr.tw |
|  4 | JoJo | Testing | Engineer |  45000 | jojo@benjr.tw |
|  5 | Ian  | PM      | Engineer |  85000 | ian@benjr.tw  |
+----+------+---------+----------+--------+---------------+
5 rows in set (0.001 sec)
  • CONCAT
    CONTACT 函數可以將兩個或更多個字串連接在一起.

    CONCAT(expression1, expression2, expression3,...)
    
    MariaDB [(none)]> SELECT CONCAT("APPLE", ", Banana", ", Cherry") AS Fruit; 
    +-----------------------+
    | Fruit                 |
    +-----------------------+
    | APPLE, Banana, Cherry |
    +-----------------------+
    1 row in set (0.00 sec)
    
  • CONCAT_WS
    相較於 CONTACT 函數 CONCAT_WS 可以加上固定的分隔字元.

    CONCAT_WS(separator, expression1, expression2, expression3,...)
    
    MariaDB [(none)]> SELECT CONCAT_WS(", ", "APPLE", "Banana", "Cherry") AS Fruit; 
    +-----------------------+
    | Fruit                 |
    +-----------------------+
    | APPLE, Banana, Cherry |
    +-----------------------+
    1 row in set (0.01 sec)
    
  • GROUP_CONCAT
    通常搭配 GROUP BY 使用,透過 GROUP_CONCAT 函數,可以把同 Group 的欄位文字整合在一起.

    GROUP_CONCAT([DISTINCT] expr [,expr ...]
                 [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
                 [SEPARATOR str_val])
    
    MariaDB [testdb]> SELECT Dept, GROUP_CONCAT(Name SEPARATOR ',') AS "Member Name" FROM employee GROUP BY Dept;
    +---------+---------------+
    | Dept    | Member Name   |
    +---------+---------------+
    | PM      | Ian           |
    | Power   | Afa           |
    | Testing | Ben,Boss,JoJo |
    +---------+---------------+
    3 rows in set (0.001 sec)
    

    如果 GROUPBY 的資料要按照順序,可以加 ORDER BY DESC 或是 ASC

    MariaDB [testdb]> SELECT Dept, GROUP_CONCAT(Name ORDER BY Name DESC SEPARATOR ',') AS "Member Name" FROM employee GROUP BY Dept;
    +---------+---------------+
    | Dept    | Member Name   |
    +---------+---------------+
    | PM      | Ian           |
    | Power   | Afa           |
    | Testing | JoJo,Boss,Ben |
    +---------+---------------+
    3 rows in set (0.001 sec)
    
    MariaDB [testdb]> SELECT Dept, GROUP_CONCAT(Name ORDER BY Name ASC SEPARATOR ',') AS "Member Name" FROM employee GROUP BY Dept;
    +---------+---------------+
    | Dept    | Member Name   |
    +---------+---------------+
    | PM      | Ian           |
    | Power   | Afa           |
    | Testing | Ben,Boss,JoJo |
    +---------+---------------+
    3 rows in set (0.001 sec)
    
  • LOCATE
    檢視字串裡面是否有要搜尋的字,substr (搜尋關鍵字), str (字串), pos(從字串的哪一個字元開始搜尋).

    LOCATE(substr,str), LOCATE(substr,str,pos)
    
    MariaDB [testdb]> SELECT LOCATE('bar', 'foobarbar') AS 'Search Result';
    +---------------+
    | Search Result |
    +---------------+
    |             4 |
    +---------------+
    1 row in set (0.000 sec)
    
    MariaDB [testdb]> SELECT LOCATE('xbar', 'foobar') AS 'Search Result';
    +---------------+
    | Search Result |
    +---------------+
    |             0 |
    +---------------+
    1 row in set (0.000 sec)
    
    MariaDB [testdb]> SELECT LOCATE('bar', 'foobarbar', 5) AS 'Search Result';
    +---------------+
    | Search Result |
    +---------------+
    |             7 |
    +---------------+
    1 row in set (0.001 sec)
    
  • TRIM
    移除特定字串.

    TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str)
    

    可以用 BOTH (全部), LEADING (字頭) , TRAILING (字尾) 來決定取代哪邊的字串.

    MariaDB [testdb]> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx') AS 'TRIM Result';
    +-------------+
    | TRIM Result |
    +-------------+
    | barxxx      |
    +-------------+
    1 row in set (0.001 sec)
    
    MariaDB [testdb]> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx') AS 'TRIM Result';
    +-------------+
    | TRIM Result |
    +-------------+
    | bar         |
    +-------------+
    1 row in set (0.000 sec)
    
    MariaDB [testdb]> SELECT TRIM(TRAILING 'x' FROM 'xxxbarxxx') AS 'TRIM Result';
    +-------------+
    | TRIM Result |
    +-------------+
    | xxxbar      |
    +-------------+
    1 row in set (0.000 sec)
    
  • REPLACE
    用 REPLACE 可以取代字串裡的特定字串.

    REPLACE(str, find_string, replace_with)
    

    \n 為 Linux 的換行字元.

    MariaDB [testdb]> SELECT '123\n456' AS ' TEXT';
    +---------+
    | TEXT    |
    +---------+
    | 123
    456 |
    +---------+
    1 row in set, 1 warning (0.001 sec)
    
    MariaDB [testdb]> SELECT REPLACE('123\n456' ,'\n' ,'') AS ' Replace Result';
    +----------------+
    | Replace Result |
    +----------------+
    | 123456         |
    +----------------+
    1 row in set, 1 warning (0.000 sec)
    
  • REGEXP_REPLACE
    可以搭配 REGEXP (正規表示式) 使用的 REPLACE (取代字串裡的特定字串),更多關於 REGEXP_REPLACE 請參考 – http://benjr.tw/102758 .

    REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]])
    

    下面範例用來剔除 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)
    
  • FIND_IN_SET
    回傳值為 string 位於 string_list (須以 , 分隔)的哪一個位置(不存在時,回傳 0 ).

    FIND_IN_SET(string, string_list)
    
    MariaDB [testdb]> SELECT FIND_IN_SET('b' , 'a,b,c') AS 'FIND_IN_SET Result';
    +--------------------+
    | FIND_IN_SET Result |
    +--------------------+
    |                  2 |
    +--------------------+
    1 row in set (0.001 sec)
    
    MariaDB [testdb]> SELECT FIND_IN_SET('f' , 'a,b,c') AS 'FIND_IN_SET Result';
    +--------------------+
    | FIND_IN_SET Result |
    +--------------------+
    |                  0 |
    +--------------------+
    1 row in set (0.001 sec)
    

    雖然 FIND_IN_SET 函數可以解決單一欄位儲存多個資料 (以欄位中的 “, comma separated ” 為分隔) 所造成的搜尋問題,但不建議儲存成這樣的欄位內容.使用資料庫正規化 (Database normalization) – http://benjr.tw/101850 重新定義 Table 與其相關性.

  • SUBSTR , SUBSTRING
    從一字串取出一段選定的字串,SUBSTR , SUBSTRING 的使用方式一樣.

    SUBSTR(string, start, length)
    

    OR:

    SUBSTR(string FROM start FOR length)
    

    從字串中取出第一個字元到第四個字元.

    MariaDB [(none)]> SELECT SUBSTR("This is a Book", 1, 4) AS ExtractString; 
    +---------------+
    | ExtractString |
    +---------------+
    | This          |
    +---------------+
    1 row in set (0.001 sec)
    
    MariaDB [(none)]> SELECT SUBSTRING("This is a Book", 1, 4) AS ExtractString; 
    +---------------+
    | ExtractString |
    +---------------+
    | This          |
    +---------------+
    1 row in set (0.000 sec)
    
    MariaDB [(none)]> SELECT SUBSTR("This is a Book" FROM 1 FOR 4) AS ExtractString; 
    +---------------+
    | ExtractString |
    +---------------+
    | This          |
    +---------------+
    1 row in set (0.000 sec)
    

    字元起始位置也可以從後面算起,-4 代俵從後面算起來第 4 個字元,並取出 4 個字元.

    MariaDB [(none)]> SELECT SUBSTR("This is a Book",-4, 4) AS ExtractString; 
    +---------------+
    | ExtractString |
    +---------------+
    | Book          |
    +---------------+
    1 row in set (0.001 sec)
    
  • SUBSTRING_INDEX
    依據自定 delimiter (分隔符號) 來從一字串中取出一段選定的字串.

    SUBSTRING_INDEX(string, delimiter, number)
    

    以空格作區分,從字串中取第兩個空格字串前的字串.

    MariaDB [(none)]> SELECT SUBSTRING_INDEX("This is a Book"," ", 2) AS ExtractString; 
    +---------------+
    | ExtractString |
    +---------------+
    | This is       |
    +---------------+
    1 row in set (0.000 sec)
    

    – (負數)代表從後面算起,-2 代表字串從後面算起取第兩個空格字串後的字串.

    MariaDB [(none)]> SELECT SUBSTRING_INDEX("This is a Book"," ", -2) AS ExtractString; 
    +---------------+
    | ExtractString |
    +---------------+
    | a Book        |
    +---------------+
    1 row in set (0.001 sec)
    
  • STR_TO_DATE()
    mysql 標準 timestamp 格式為 yyyy-mm-dd hh:mm:ss (24 小時制),如果從程式讀取的格式不同就可以透過這個函數作轉換.

    STR_TO_DATE(string, format)
    
    MariaDB [testdb]> SELECT STR_TO_DATE("2/25/2020 11:13:03 PM", "%m/%d/%Y %h:%i:%s %p") AS TimeStamp; 
    +---------------------+
    | TimeStamp           |
    +---------------------+
    | 2020-02-25 23:13:03 |
    +---------------------+
    1 row in set (0.000 sec)
    

    更多參數請參考 – https://mariadb.com/kb/en/str_to_date/

    • %m : Month with 2 digits.
    • %d : Day with 2 digits.
    • %Y : Year with 4 digits.
    • %h : Hour with 2 digits between 01-12.
    • %i : Minute with 2 digits.
    • %s : Seconds with 2 digits.
    • %p : AM/PM according to current locale.
  • QUOTE 函數
    QUOTE 函數有以下功能.

    1. 帶有單引號 ‘ , 反斜線 \ ,ASCII NUL 以及 Control-Z 帶有反斜線.
    2. 如果值是 NULL,則傳回為字串 “NULL”

    在 SQL 的字串不能使用 ” (Double Quote) 或是 ‘ (Single Quote) 來表示,但可以使用 \ 反斜線 (Backslash Character) 這個跳脫字元來表示 (” 需用 \” , ‘ 需用 \’) – http://benjr.tw/102928 ,使用 QUOTE 函數可以還原原本資料.

    MariaDB [testdb]> SELECT 'I\'m';
    +-----+
    | I'm |
    +-----+
    | I'm |
    +-----+
    1 row in set (0.000 sec)
    
    MariaDB [testdb]> SELECT QUOTE('I\'m');
    +---------------+
    | QUOTE('I\'m') |
    +---------------+
    | 'I\'m'        |
    +---------------+
    1 row in set (0.000 sec)
    

    傳回為字串 “NULL” .

    MariaDB [testdb]> SELECT QUOTE(NULL); 
    +-------------+
    | QUOTE(NULL) |
    +-------------+
    | NULL        |
    +-------------+
    1 row in set (0.001 sec)
    
  • CAST
    資料型別作轉換.

    CAST(expr AS type)
    
    MariaDB [(none)]> SELECT CAST(1.09 AS SIGNED);
    +----------------------+
    | CAST(1.09 AS SIGNED) |
    +----------------------+
    |                    1 |
    +----------------------+
    1 row in set (0.00 sec)
    

    可轉換型別如下
    BINARY , CHAR , DATE , TIME , DATETIME , SIGNED [INTEGER] ,UNSIGNED [INTEGER]

  • CONVERT
    資料型別作轉換.其中 CONVERT(expr, type) 與 CAST(expr AS type) 使用上是一樣的.

    CONVERT(expr,type), CONVERT(expr USING transcoding_name)
    
    MariaDB [(none)]> SELECT CONVERT(1.09,SIGNED);
    +----------------------+
    | CONVERT(1.09,SIGNED) |
    +----------------------+
    |                    1 |
    +----------------------+
    1 row in set (0.00 sec)
    

    CONVERT(expr USING transcoding_name) 用在不同的字符集轉換上.

    MariaDB [(none)]> SELECT CONVERT('abc' USING utf8);
    +---------------------------+
    | CONVERT('abc' USING utf8) |
    +---------------------------+
    | abc                       |
    +---------------------------+
    1 row in set (0.00 sec)
    
  • LOAD_FILE
    LOAD_FILE 函數指定載入檔案位置(只限定位於 MySQL Server Host 上的檔案),需注意權限問題(該使用者需有 File 的權限,可使用 SQL 指令 SHOW PRIVILEGES; 來檢視),無法讀取時 LOAD_FILE 函數會回傳 NULL .得到 NULL 代表 mysql 使用者無法讀取該檔案,這時要注意存放檔案的目錄需要可以進入 (a+x) 的權限,檔案本身至少需要可供讀取 (a+r) 的權限.

    另外一個可能是當檔案大小超出 MYSQL 的預設時 max_allowed_packet 也無法存取,可以使用SQL 指令 SHOW VARIABLES LIKE ‘max_allowed_packet’; 來檢視.

    MariaDB [(none)]> SHOW VARIABLES LIKE 'max_allowed_packet';
    +--------------------+---------+
    | Variable_name      | Value   |
    +--------------------+---------+
    | max_allowed_packet | 1048576 |
    +--------------------+---------+
    1 row in set (0.00 sec)
    
    [root@localhost ~]# cat /var/lib/mysql/test.txt 
    ABC
    123
    [root@localhost ~]# mysql -u root -p
    Enter password: 
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 3
    Server version: 5.5.60-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.
    
    MariaDB [(none)]> SELECT LOAD_FILE('/var/lib/mysql/test.txt');
    +--------------------------------------+
    | LOAD_FILE('/var/lib/mysql/test.txt') |
    +--------------------------------------+
    | ABC
    123
                                 |
    +--------------------------------------+
    1 row in set (0.01 sec)
    

    更多關於 Load 載入資料到資料表,請參考 – http://benjr.tw/101779

  • LOAD
    現在透過 SQL 語法 LOAD DATA INFILE 把檔案匯入到資料庫,下面範例使用 /etc/passwd .

    MariaDB [dbtest]> LOAD DATA INFILE '/etc/passwd' INTO TABLE testdb.passwd FIELDS TERMINATED BY ':' ENCLOSED BY '"' LINES TERMINATED BY '\n' (Username , Password , UID  , GID , IDInfo , HomeDir , Shell) ;
    

    更多關於 LOAD DATA 請參考 – http://benjr.tw/103012

  • ExtractValue
    當我們把整個 XML 儲存在一個欄位 (建議使用 TEXT 資料型態欄位),可以再透過函數 ExtractValue 取出相對應的資料,更多關於 ExtractValue 請參考 – http://benjr.tw/102790
    要怎麼透過函數 ExtractValue 把 XML 格式的資料擷取出來呢!函數 ExtractValue 參數需指定 XPath 其格式如下.

    /tag

    位於最上(外)層 (Root Element , 根元素 , 父元素) 且名稱匹配 <tag/> <tag/> 內的資料.

    /tag1/tag2

    除了要匹配 <tag2> <tag2/> 外,還必須為 <tag1> <tag1/> 最上(外)層 (Root Element , 根元素 , 父元素) 的子元素.

    MariaDB [testdb]> SET @xml = '<a><b>X</b><b>Y</b></a>';
    Query OK, 0 rows affected (0.001 sec)
    
    MariaDB [testdb]> SELECT ExtractValue(@xml, '/a/b[1]');
    +-------------------------------+
    | ExtractValue(@xml, '/a/b[1]') |
    +-------------------------------+
    | X                             |
    +-------------------------------+
    1 row in set (0.000 sec)
    
    //tag

    匹配任何為 <tag> 的資料.

    MariaDB [testdb]> SELECT ExtractValue(@xml, '//b[1]');
    +------------------------------+
    | ExtractValue(@xml, '//b[1]') |
    +------------------------------+
    | X                            |
    +------------------------------+
    1 row in set (0.000 sec)
    
    MariaDB [testdb]> SELECT ExtractValue(@xml, '//b[2]');
    +------------------------------+
    | ExtractValue(@xml, '//b[2]') |
    +------------------------------+
    | Y                            |
    +------------------------------+
    1 row in set (0.000 sec)
    
    No Matching Element

    如果沒符合的資料時,並不是回傳 NULL 而是 “”(空字串),要怎麼判斷是否沒資料(回傳空字串)還是真的是得到的是空字串.

    MariaDB> SELECT ExtractValue('<a><b/></a>', '/a/b');
    +-------------------------------------+
    | ExtractValue('<a><b/></a>', '/a/b') |
    +-------------------------------------+
    |                                     |
    +-------------------------------------+
    1 row in set (0.00 sec)
    
    MariaDB> SELECT ExtractValue('<a><c/></a>', '/a/b');
    +-------------------------------------+
    | ExtractValue('<a><c/></a>', '/a/b') |
    +-------------------------------------+
    |                                     |
    +-------------------------------------+
    1 row in set (0.00 sec)
    
    上面範例一回傳的是有資料(內容物為空字串),範例二是沒有資料(一樣回傳空字串),我們要怎麼判斷,可以利用 COUNT() 函數.

    MariaDB> SELECT ExtractValue('<a><b/></a>', 'count(/a/b)');
    +-------------------------------------+
    | ExtractValue('<a><b/></a>', 'count(/a/b)') |
    +-------------------------------------+
    | 1                                   |
    +-------------------------------------+
    1 row in set (0.00 sec)
    
    MariaDB> SELECT ExtractValue('<a><c/></a>', 'count(/a/b)');
    +-------------------------------------+
    | ExtractValue('<a><c/></a>', 'count(/a/b)') |
    +-------------------------------------+
    | 0                                   |
    +-------------------------------------+
    1 row in set (0.01 sec)
    
    有資料的 COUNT() 為 1 ,沒資料的是 0 .
沒有解決問題,試試搜尋本站其他內容

發佈留言

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

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