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

Loading

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

  1. 字串函數 ( String Functions ) 與 型別轉換 – 這邊介紹.
  2. 數字函數 (Numeric Functions)https://benjr.tw/101970
  3. 日期與時間函數 (Date & Time Functions)https://benjr.tw/102964
  4. 資訊函數 (Information Functions)https://benjr.tw/102961
  5. 搭配 GROUP BY 函數 (Aggregate Functions)https://benjr.tw/102967
  6. 流程控制函數 (Control Flow Functions)https://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 ,請參考資料庫編碼 – https://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)
  • RIGHT & LEFT
    RIGHT (從右邊) 與 LEFT (從左邊) 擷取指定字元數量.

    MariaDB [(none)]> SELECT RIGHT('MariaDB', 5);
    +---------------------+
    | RIGHT('MariaDB', 5) |
    +---------------------+
    | riaDB               |
    +---------------------+
    1 row in set (0.000 sec)
    
    MariaDB [(none)]> SELECT LEFT('MariaDB', 5);
    +--------------------+
    | LEFT('MariaDB', 5) |
    +--------------------+
    | Maria              |
    +--------------------+
    1 row in set (0.000 sec)
    
  • POSITION & INSTR
    POSITION 與 INSTR 函數可以查看子字串在字串的位置.

    MariaDB [(none)]> SELECT POSITION("DB" IN "MariaDB");
    +-----------------------------+
    | POSITION("DB" IN "MariaDB") |
    +-----------------------------+
    |                           6 |
    +-----------------------------+
    1 row in set (0.000 sec)
    
    MariaDB [(none)]> SELECT INSTR('MariaDB', 'DB');
    +------------------------+
    | INSTR('MariaDB', 'DB') |
    +------------------------+
    |                      6 |
    +------------------------+
    1 row in set (0.000 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)
    

    要注意使用 CONTACT 函數中有 NULL 時跟任何字串連接在一起還是 NULL.

    MariaDB [(none)]> SELECT CONCAT(NULL, ", Banana", ", Cherry") AS Fruit; 
    +-------+
    | Fruit |
    +-------+
    | NULL  |
    +-------+
    1 row in set (0.001 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 請參考 – https://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) – https://benjr.tw/101850 重新定義 Table 與其相關性或是使用 JSON 格式來儲存 https://benjr.tw/103200 .

  • 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.
  • CHAR_LENGTH , LENGTH
    計算字元有幾個.

    MariaDB [(none)]> SELECT CHAR_LENGTH('ABC');
    +--------------------+
    | CHAR_LENGTH('ABC') |
    +--------------------+
    |                  3 |
    +--------------------+
    1 row in set (0.000 sec)
    
    MariaDB [(none)]> SELECT LENGTH('ABC');
    +---------------+
    | LENGTH('ABC') |
    +---------------+
    |             3 |
    +---------------+
    1 row in set (0.000 sec)
    
  • QUOTE 函數
    QUOTE 函數有以下功能.

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

    在 SQL 的字串不能使用 ” (Double Quote) 或是 ‘ (Single Quote) 來表示,但可以使用 \ 反斜線 (Backslash Character) 這個跳脫字元來表示 (” 需用 \” , ‘ 需用 \’) – https://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)
    
沒有解決問題,試試搜尋本站其他內容

發佈留言

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

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