MySQL 字串函式大全

神諭丶發表於2015-10-09
MySql字串函式
以下內容基於MySQL 5.6及更高，大部分函式5.5也基本適用，更低版本請參考對應版本手冊，其內容整理自官方。

mysql常用函式被分為五類，分別為：

①String：字串函式；

②Date and time：日期和時間；
http://blog.itpub.net/29773961/viewspace-1808967/

③Numeric：數字函式；
http://blog.itpub.net/29773961/viewspace-1813556/

④Control Flow：基於一個表示式的結果集選擇不同的值（控制流函式）；
http://blog.itpub.net/29773961/viewspace-1813557/

⑤Aggregate：基於一列的多個值返回單一值（聚合函式）；
http://blog.itpub.net/29773961/viewspace-1813589/

【String】：
ASCII()：返回ASCII碼
  1. mysql> SELECT ASCII('A');
  2. +------------+
  3. | ASCII('A') |
  4. +------------+
  5. | 65         |
  6. +------------+
  7. 1 row in set (0.00 sec)


BIN()：返回二進位制值，非數字則返回0
  1. mysql> SELECT BIN('HELLO'), BIN('8'), BIN(10);
  2. +--------------+----------+---------+
  3. | BIN('HELLO') | BIN('8') | BIN(10) |
  4. +--------------+----------+---------+
  5. | 0            | 1000     | 1010    |
  6. +--------------+----------+---------+
  7. 1 row in set (0.00 sec)


BIT_LENGTH()：以bit為單位來返回字串長度
  1. mysql> SELECT BIT_LENGTH('a'), BIT_LENGTH('HELLO');
  2. +-----------------+---------------------+
  3. | BIT_LENGTH('a') | BIT_LENGTH('HELLO') |
  4. +-----------------+---------------------+
  5. | 8               | 40                  |
  6. +-----------------+---------------------+
  7. 1 row in set (0.00 sec)


CHAR_LENGTH()與CHARACTER_LENGTH()：返回字串的字元數
  1. mysql> SELECT CHAR_LENGTH('hello world');
  2. +----------------------------+
  3. | CHAR_LENGTH('hello world') |
  4. +----------------------------+
  5. | 11                         |
  6. +----------------------------+
  7. 1 row in set (0.00 sec)


CHAR()：將傳入的數轉換成正數並返回ACSII對應的字元
  1. mysql> SELECT CHAR(77,'121.9',83*1,81.3,'76');
  2. +---------------------------------+
  3. | CHAR(77,'121.9',83*1,81.3,'76') |
  4. +---------------------------------+
  5. | MySQL                           |
  6. +---------------------------------+
  7. 1 row in set, 1 warning (0.00 sec)

  9. mysql> SHOW WARNINGS;
  10. +---------+------+--------------------------------------------+
  11. | Level   | Code | Message                                    |
  12. +---------+------+--------------------------------------------+
  13. | Warning | 1292 | Truncated incorrect INTEGER value: '121.9' |
  14. +---------+------+--------------------------------------------+
  15. 1 row in set (0.00 sec)
  1. mysql> SELECT CHARSET(CHAR(0x65)), CHARSET(CHAR(0x65 USING utf8));
  2. +---------------------+--------------------------------+
  3. | CHARSET(CHAR(0x65)) | CHARSET(CHAR(0x65 USING utf8)) |
  4. +---------------------+--------------------------------+
  5. | binary              | utf8                           |
  6. +---------------------+--------------------------------+
  7. 1 row in set (0.00 sec)


CONCAT_WS()：用特定字元連線引數組成一個字串（拼接字串）
  1. mysql> SELECT CONCAT_WS('_','hello','my','sql');
  2. +-----------------------------------+
  3. | CONCAT_WS('_','hello','my','sql') |
  4. +-----------------------------------+
  5. | hello_my_sql                      |
  6. +-----------------------------------+
  7. 1 row in set (0.00 sec)


CONCAT()：連線傳入的引數成一個字串（拼接字串）
  1. mysql> SELECT CONCAT('m','y','sql');
  2. +-----------------------+
  3. | CONCAT('m','y','sql') |
  4. +-----------------------+
  5. | mysql                 |
  6. +-----------------------+
  7. 1 row in set (0.00 sec)


ELT()：返回列表的第n個元素
  1. mysql> SELECT ELT(1,'index1','index2','index3'),
  2.     -> ELT(3,'index1','index2','index3');
  3. +-----------------------------------+-----------------------------------+
  4. | ELT(1,'index1','index2','index3') | ELT(3,'index1','index2','index3') |
  5. +-----------------------------------+-----------------------------------+
  6. | index1                            | index3                            |
  7. +-----------------------------------+-----------------------------------+
  8. 1 row in set (0.01 sec)


EXPORT_SET(bits,expr1,expr2[,'分隔符','用於補零'])：按bit的排列方式，當位等於1時，插入expr1，等於0時，插入expr2，從左向右排列字元。
  1. mysql> SELECT EXPORT_SET(2,'Y','N','_',5), EXPORT_SET(3,'Y','N','_',5), EXPORT_SET(4,'Y','N',',',5);
  2. +-----------------------------+-----------------------------+-----------------------------+
  3. | EXPORT_SET(2,'Y','N','_',5) | EXPORT_SET(3,'Y','N','_',5) | EXPORT_SET(4,'Y','N',',',5) |
  4. +-----------------------------+-----------------------------+-----------------------------+
  5. | N_Y_N_N_N                   | Y_Y_N_N_N                   | N,N,Y,N,N                   |
  6. +-----------------------------+-----------------------------+-----------------------------+
  7. 1 row in set (0.00 sec)


FIELD(v,v1,v2....)：v值去與v1,v2……匹配，匹配到後返回該值位置，若無，則返回0
  1. mysql> SELECT FIELD('hey','un','hey','huh','hey'),
  2.     -> FIELD('bye','un','hey','huh','hey');
  3. +-------------------------------------+-------------------------------------+
  4. | FIELD('hey','un','hey','huh','hey') | FIELD('bye','un','hey','huh','hey') |
  5. +-------------------------------------+-------------------------------------+
  6. | 2                                   | 0                                   |
  7. +-------------------------------------+-------------------------------------+
  8. 1 row in set (0.00 sec)


FIND_IN_SET()：查詢字元在字串中的位置
  1. mysql> SELECT FIND_IN_SET('b','a,b,c'),
  2.     -> FIND_IN_SET('y','a,b,c');
  3. +--------------------------+--------------------------+
  4. | FIND_IN_SET('b','a,b,c') | FIND_IN_SET('y','a,b,c') |
  5. +--------------------------+--------------------------+
  6. | 2                        | 0                        |
  7. +--------------------------+--------------------------+
  8. 1 row in set (0.00 sec)


FORMAT()：將數字格式化成形如#,###,###形式，可限制其小數位，若不足則補0
  1. mysql> SELECT FORMAT(123456,2),
  2.     -> FORMAT(1234567.333,1);
  3. +------------------+-----------------------+
  4. | FORMAT(123456,2) | FORMAT(1234567.333,1) |
  5. +------------------+-----------------------+
  6. | 123,456.00       | 1,234,567.3           |
  7. +------------------+-----------------------+
  8. 1 row in set (0.00 sec)


FROM_BASE64()：base64解碼
TO_BASE64()：base64解碼
  1. mysql> SELECT TO_BASE64('abc'), FROM_BASE64(TO_BASE64('abc'));
  2. +------------------+-------------------------------+
  3. | TO_BASE64('abc') | FROM_BASE64(TO_BASE64('abc')) |
  4. +------------------+-------------------------------+
  5. | YWJj             | abc                           |
  6. +------------------+-------------------------------+
  7. 1 row in set (0.00 sec)


HEX()：16進位制編碼
UNHEX()：16進位制解碼
  1. mysql> SELECT 0x616263, HEX('abc'), UNHEX(HEX('abc'));
  2. +----------+------------+-------------------+
  3. | 0x616263 | HEX('abc') | UNHEX(HEX('abc')) |
  4. +----------+------------+-------------------+
  5. | abc      | 616263     | abc               |
  6. +----------+------------+-------------------+
  7. 1 row in set (0.00 sec)


INSERT()：在指定位置替換字元/字串
  1. mysql> SELECT INSERT('Quadratic',3,4,'What'),
  2.     -> INSERT('Quadratic',-1,4,'What'),
  3.     -> INSERT('Quadratic',3,100,'What');
  4. +--------------------------------+---------------------------------+----------------------------------+
  5. | INSERT('Quadratic',3,4,'What') | INSERT('Quadratic',-1,4,'What') | INSERT('Quadratic',3,100,'What') |
  6. +--------------------------------+---------------------------------+----------------------------------+
  7. | QuWhattic                      | Quadratic                       | QuWhat                           |
  8. +--------------------------------+---------------------------------+----------------------------------+
  9. 1 row in set (0.00 sec)


INSTR()：返回字串的位置，類似POSITION()與LOCATION()
  1. mysql> SELECT INSTR('mysql','ql');
  2. +---------------------+
  3. | INSTR('mysql','ql') |
  4. +---------------------+
  5. | 4                   |
  6. +---------------------+
  7. 1 row in set (0.00 sec)


LEFT()：返回字串左側的指定字元數的字串
  1. mysql> SELECT LEFT('mysql',2);
  2. +-----------------+
  3. | LEFT('mysql',2) |
  4. +-----------------+
  5. | my              |
  6. +-----------------+
  7. 1 row in set (0.00 sec)


LENGTH()與OCTET_LENGTH()：返回字串的字元數
  1. mysql> SELECT LENGTH('mysql'), LENGTH('嗯');
  2. +-----------------+--------------+
  3. | LENGTH('mysql') | LENGTH('嗯') |
  4. +-----------------+--------------+
  5. | 5               | 3            |
  6. +-----------------+--------------+
  7. 1 row in set (0.00 sec)


LOCATE()與POSITION()：返回字串所在的位置，類似INSTR。
  1. mysql> SELECT LOCATE('ql','mysql');
  2. +----------------------+
  3. | LOCATE('ql','mysql') |
  4. +----------------------+
  5. | 4                    |
  6. +----------------------+
  7. 1 row in set (0.00 sec)


LOWER()與LCASE()：返回所有字串的小寫形式
  1. mysql> SELECT LOWER('MySQL');
  2. +----------------+
  3. | LOWER('MySQL') |
  4. +----------------+
  5. | mysql          |
  6. +----------------+
  7. 1 row in set (0.00 sec)
  1. mysql> SELECT LCASE('MYSQL');
  2. +----------------+
  3. | LCASE('MYSQL') |
  4. +----------------+
  5. | mysql          |
  6. +----------------+
  7. 1 row in set (0.00 sec)


LPAD()：返回指定長度的字串，在左側用指定字元填充。
  1. mysql> SELECT LPAD('MySQL',10,'a');
  2. +----------------------+
  3. | LPAD('MySQL',10,'a') |
  4. +----------------------+
  5. | aaaaaMySQL           |
  6. +----------------------+
  7. 1 row in set (0.00 sec)


LTRIM()與RTRIM()：在左側或右側去除空格並返回字串，類似於TRIM()
  1. mysql> SELECT LTRIM(' _mysql') a, RTRIM('mysql_ ') b;
  2. +--------+--------+
  3. | a      | b      |
  4. +--------+--------+
  5. | _mysql | mysql_ |
  6. +--------+--------+
  7. 1 row in set (0.00 sec)


MAKE_SET()：按bit的排列方式，當位等於1時，返回該值，等於0時，不返回，從左向右排列字元。
  1. mysql> SELECT MAKE_SET(2,'a','b','c','d'), 
  2.  -> MAKE_SET(3,'a','b','c','d'), 
  3.  -> MAKE_SET(4,'a','b','c','d'), 
  4.  -> MAKE_SET(5,'a','b','c','d')\G
  5. *************************** 1. row ***************************
  6. MAKE_SET(2,'a','b','c','d'): b
  7. MAKE_SET(3,'a','b','c','d'): a,b
  8. MAKE_SET(4,'a','b','c','d'): c
  9. MAKE_SET(5,'a','b','c','d'): a,c
  10. 1 row in set (0.00 sec)


OCT()：八進位制編碼
  1. mysql> SELECT OCT(12);
  2. +---------+
  3. | OCT(12) |
  4. +---------+
  5. | 14      |
  6. +---------+
  7. 1 row in set (0.00 sec)


ORD()：返回字串的第一個字元的ASCII碼
  1. mysql> SELECT ORD('a'), ORD('all'), ASCII('a');
  2. +----------+------------+------------+
  3. | ORD('a') | ORD('all') | ASCII('a') |
  4. +----------+------------+------------+
  5. | 97       | 97         | 97         |
  6. +----------+------------+------------+
  7. 1 row in set (0.00 sec)


QUOTE()：用單引號將整個字串包裹起來，併為字串本身的特殊字元增加跳脫字元
  1. mysql> SELECT QUOTE('Don\'t!'),
  2.     -> QUOTE('\t');
  3. +------------------+-------------+
  4. | QUOTE('Don\'t!') | QUOTE('\t') |
  5. +------------------+-------------+
  6. | 'Don\'t!'        | '    '      |


REPEAT()：重複輸出字串
  1. mysql> SELECT REPEAT('mysql',5);
  2. +---------------------------+
  3. | REPEAT('mysql',5)         |
  4. +---------------------------+
  5. | mysqlmysqlmysqlmysqlmysql |
  6. +---------------------------+
  7. 1 row in set (0.00 sec)


REPLACE()：將字串中的字元(串)替換成另一個字元(串)
  1. mysql> SELECT REPLACE('','com','cn');
  2. +--------------------------------------+
  3. | REPLACE('','com','cn') |
  4. +--------------------------------------+
  5. | www.google.cn                        |
  6. +--------------------------------------+
  7. 1 row in set (0.00 sec)


REVERSE()：返回字串以置返的形式（置返字串）
  1. mysql> SELECT REVERSE('123456');
  2. +-------------------+
  3. | REVERSE('123456') |
  4. +-------------------+
  5. | 654321            |
  6. +-------------------+
  7. 1 row in set (0.00 sec)


RIGHT()：返回字串右側的指定字元數的字串
  1. mysql> SELECT RIGHT('mysql',3);
  2. +------------------+
  3. | RIGHT('mysql',3) |
  4. +------------------+
  5. | sql              |
  6. +------------------+
  7. 1 row in set (0.00 sec)


RPAD()：返回指定長度的字串，在右側用指定字元填充。
  1. mysql> SELECT RPAD('MySQL',10,'_');
  2. +----------------------+
  3. | RPAD('MySQL',10,'_') |
  4. +----------------------+
  5. | MySQL_____           |
  6. +----------------------+
  7. 1 row in set (0.00 sec)


SOUNDEX()：返回soundex字串（標準的為4字元），但是該函式可能返回一個稍長一點的字串，利用字元的讀音近似值所得。
目前是一個有限制的函式，只能用於英語，其他語言會產生無法預期的結果，官方表示將在未來版本（5.6以後）移除這些限制，可以在官方查詢BUG #22638來獲得更多資訊。
  1. mysql> SELECT SOUNDEX('HELLO'), SOUNDEX('Quadratically');
  2. +------------------+--------------------------+
  3. | SOUNDEX('HELLO') | SOUNDEX('Quadratically') |
  4. +------------------+--------------------------+
  5. | H400             | Q36324                   |
  6. +------------------+--------------------------+
  7. 1 row in set (0.00 sec)


SPACE()：返回N個空格
  1. mysql> SELECT SPACE(10) a, LENGTH(SPACE(10));
  2. +------------+-------------------+
  3. | a          | LENGTH(SPACE(10)) |
  4. +------------+-------------------+
  5. |            | 10                |
  6. +------------+-------------------+
  7. 1 row in set (0.00 sec)


STRCMP()：返回字串的比較結果，0=same，-1=smaller，1=other（結果說明不分大小寫）
  1. mysql> SELECT STRCMP('mysql','MYSQL'), STRCMP('mysql','mysql'), STRCMP('mysql','oracle'), STRCMP('oracle','mysql')\G
  2. *************************** 1. row ***************************
  3.  STRCMP('mysql','MYSQL'): 0
  4.  STRCMP('mysql','mysql'): 0
  5.  STRCMP('mysql','oracle'): -1
  6.  STRCMP('oracle','mysql'): 1
  7. 1 row in set (0.00 sec)


SUBSTRING()與MID()與SUBSTR()：：返回從指定位置開始的字串
  1. mysql> SELECT SUBSTRING(123456,3);    -- 可以用於數字
  2. +-----------------------+
  3. | SUBSTRING(123456,'3') |
  4. +-----------------------+
  5. | 3456                  |
  6. +-----------------------+
  7. 1 row in set (0.00 sec)

  9. mysql> SELECT SUBSTRING('ABCDEFG',3);    -- 可以用於字串
  10. +--------------------------+
  11. | SUBSTRING('ABCDEFG','3') |
  12. +--------------------------+
  13. | CDEFG                    |
  14. +--------------------------+
  15. 1 row in set (0.00 sec)
  1. mysql> SELECT SUBSTRING('ABCDEFG',3,2);    -- 也可以指定返回幾個，比如從3號位置開始返回，返回2個字元
  2. +--------------------------+
  3. | SUBSTRING('ABCDEFG',3,2) |
  4. +--------------------------+
  5. | CD                       |
  6. +--------------------------+
  7. 1 row in set (0.00 sec)


SUBSTRING_INDEX()：返回字串的部分，基於指定分隔符。（如果是正數就從左往右開始搜尋，反之亦然）
  1. mysql> SELECT SUBSTRING_INDEX('abc@email.com','@',2);    -- 以@為分隔符，返回第二個整部分的字串
  2. +----------------------------------------+
  3. | SUBSTRING_INDEX('abc@email.com','@',2) |
  4. +----------------------------------------+
  5. | abc@email.com                          |
  6. +----------------------------------------+
  7. 1 row in set (0.00 sec)

  9. mysql> SELECT SUBSTRING_INDEX('abc@email.com','@',1);    -- 以@為分隔符，返回第一部分的字串
  10. +----------------------------------------+
  11. | SUBSTRING_INDEX('abc@email.com','@',1) |
  12. +----------------------------------------+
  13. | abc                                    |
  14. +----------------------------------------+
  15. 1 row in set (0.00 sec)
  1. mysql> SELECT SUBSTRING_INDEX('','.',1);    -- 以.為分隔符，返回.之前的第一部分字串
  2. +----------------------------------------+
  3. | SUBSTRING_INDEX('','.',1) |
  4. +----------------------------------------+
  5. | www                                    |
  6. +----------------------------------------+
  7. 1 row in set (0.00 sec)

  9. mysql> SELECT SUBSTRING_INDEX('','.',-1);    -- 以.為分隔符，從右往左開始，返回.之前的第一部分字串
  10. +-----------------------------------------+
  11. | SUBSTRING_INDEX('','.',-1) |
  12. +-----------------------------------------+
  13. | com                                     |
  14. +-----------------------------------------+
  15. 1 row in set (0.00 sec)

  17. mysql> SELECT SUBSTRING_INDEX('','.',-2);    -- 以.為分隔符，從右往左開始，返回.之前的第二個整部分字串
  18. +-----------------------------------------+
  19. | SUBSTRING_INDEX('','.',-2) |
  20. +-----------------------------------------+
  21. | mysql.com                               |
  22. +-----------------------------------------+
  23. 1 row in set (0.00 sec)

  25. mysql> SELECT SUBSTRING_INDEX('','.',-3);    -- 以.為分隔符，從右往左開始，返回.之前的第三個整部分字串
  26. +-----------------------------------------+
  27. | SUBSTRING_INDEX('','.',-3) |
  28. +-----------------------------------------+
  29. | www.mysql.com                           |
  30. +-----------------------------------------+
  31. 1 row in set (0.00 sec)


TRIM()：移除字串的空格或指定字元
  1. mysql> SELECT TRIM('   mysql       ') as A; -- 預設移除空格
  2. +-------+
  3. | A     |
  4. +-------+
  5. | mysql |
  6. +-------+
  7. 1 row in set (0.00 sec)

  9. mysql> SELECT TRIM(TRAILING 'asd' FROM 'mysqlasdasd') as A; -- TRAILING用來移除字串末尾的指定字元
  10. +-------+
  11. | A     |
  12. +-------+
  13. | mysql |
  14. +-------+
  15. 1 row in set (0.00 sec)

  17. mysql> SELECT TRIM(LEADING 'asd' FROM 'asdmysql') as A; -- LEADING用來移除字串開始的指定字元
  18. +-------+
  19. | A     |
  20. +-------+
  21. | mysql |
  22. +-------+
  23. 1 row in set (0.00 sec)


UPPER()與UCASE()：返回所有字串的大寫形式
  1. mysql> SELECT UPPER('MySQL');
  2. +----------------+
  3. | UPPER('MySQL') |
  4. +----------------+
  5. | MYSQL          |
  6. +----------------+
  7. 1 row in set (0.00 sec)


WEIGHT_STRING()：返回一個weight_string值
常用於測試或除錯collation。

作者公眾號（持續更新）

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29773961/viewspace-1813545/，如需轉載，請註明出處，否則將追究法律責任。

相關文章