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碼

mysql> SELECT ASCII('A');
+------------+
| ASCII('A') |
+------------+
| 65 |
+------------+
1 row in set (0.00 sec)

BIN()：返回二進位制值，非數字則返回0

mysql> SELECT BIN('HELLO'), BIN('8'), BIN(10);
+--------------+----------+---------+
| BIN('HELLO') | BIN('8') | BIN(10) |
+--------------+----------+---------+
| 0 | 1000 | 1010 |
+--------------+----------+---------+
1 row in set (0.00 sec)

BIT_LENGTH()：以bit為單位來返回字串長度

mysql> SELECT BIT_LENGTH('a'), BIT_LENGTH('HELLO');
+-----------------+---------------------+
| BIT_LENGTH('a') | BIT_LENGTH('HELLO') |
+-----------------+---------------------+
| 8 | 40 |
+-----------------+---------------------+
1 row in set (0.00 sec)

CHAR_LENGTH()與CHARACTER_LENGTH()：返回字串的字元數

mysql> SELECT CHAR_LENGTH('hello world');
+----------------------------+
| CHAR_LENGTH('hello world') |
+----------------------------+
| 11 |
+----------------------------+
1 row in set (0.00 sec)

CHAR()：將傳入的數轉換成正數並返回ACSII對應的字元

mysql> SELECT CHAR(77,'121.9',83*1,81.3,'76');
+---------------------------------+
| CHAR(77,'121.9',83*1,81.3,'76') |
+---------------------------------+
| MySQL |
+---------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: '121.9' |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CHARSET(CHAR(0x65)), CHARSET(CHAR(0x65 USING utf8));
+---------------------+--------------------------------+
| CHARSET(CHAR(0x65)) | CHARSET(CHAR(0x65 USING utf8)) |
+---------------------+--------------------------------+
| binary | utf8 |
+---------------------+--------------------------------+
1 row in set (0.00 sec)

CONCAT_WS()：用特定字元連線引數組成一個字串（拼接字串）

mysql> SELECT CONCAT_WS('_','hello','my','sql');
+-----------------------------------+
| CONCAT_WS('_','hello','my','sql') |
+-----------------------------------+
| hello_my_sql |
+-----------------------------------+
1 row in set (0.00 sec)

CONCAT()：連線傳入的引數成一個字串（拼接字串）

mysql> SELECT CONCAT('m','y','sql');
+-----------------------+
| CONCAT('m','y','sql') |
+-----------------------+
| mysql |
+-----------------------+
1 row in set (0.00 sec)

ELT()：返回列表的第n個元素

mysql> SELECT ELT(1,'index1','index2','index3'),
-> ELT(3,'index1','index2','index3');
+-----------------------------------+-----------------------------------+
| ELT(1,'index1','index2','index3') | ELT(3,'index1','index2','index3') |
+-----------------------------------+-----------------------------------+
| index1 | index3 |
+-----------------------------------+-----------------------------------+
1 row in set (0.01 sec)

EXPORT_SET(bits,expr1,expr2[,'分隔符','用於補零'])：按bit的排列方式，當位等於1時，插入expr1，等於0時，插入expr2，從左向右排列字元。

mysql> SELECT EXPORT_SET(2,'Y','N','_',5), EXPORT_SET(3,'Y','N','_',5), EXPORT_SET(4,'Y','N',',',5);
+-----------------------------+-----------------------------+-----------------------------+
| EXPORT_SET(2,'Y','N','_',5) | EXPORT_SET(3,'Y','N','_',5) | EXPORT_SET(4,'Y','N',',',5) |
+-----------------------------+-----------------------------+-----------------------------+
| N_Y_N_N_N | Y_Y_N_N_N | N,N,Y,N,N |
+-----------------------------+-----------------------------+-----------------------------+
1 row in set (0.00 sec)

FIELD(v,v1,v2....)：v值去與v1,v2……匹配，匹配到後返回該值位置，若無，則返回0

mysql> SELECT FIELD('hey','un','hey','huh','hey'),
-> FIELD('bye','un','hey','huh','hey');
+-------------------------------------+-------------------------------------+
| FIELD('hey','un','hey','huh','hey') | FIELD('bye','un','hey','huh','hey') |
+-------------------------------------+-------------------------------------+
| 2 | 0 |
+-------------------------------------+-------------------------------------+
1 row in set (0.00 sec)

FIND_IN_SET()：查詢字元在字串中的位置

mysql> SELECT FIND_IN_SET('b','a,b,c'),
-> FIND_IN_SET('y','a,b,c');
+--------------------------+--------------------------+
| FIND_IN_SET('b','a,b,c') | FIND_IN_SET('y','a,b,c') |
+--------------------------+--------------------------+
| 2 | 0 |
+--------------------------+--------------------------+
1 row in set (0.00 sec)

FORMAT()：將數字格式化成形如#,###,###形式，可限制其小數位，若不足則補0

mysql> SELECT FORMAT(123456,2),
-> FORMAT(1234567.333,1);
+------------------+-----------------------+
| FORMAT(123456,2) | FORMAT(1234567.333,1) |
+------------------+-----------------------+
| 123,456.00 | 1,234,567.3 |
+------------------+-----------------------+
1 row in set (0.00 sec)

FROM_BASE64()：base64解碼
TO_BASE64()：base64解碼

mysql> SELECT TO_BASE64('abc'), FROM_BASE64(TO_BASE64('abc'));

+------------------+-------------------------------+

| TO_BASE64('abc') | FROM_BASE64(TO_BASE64('abc')) |

+------------------+-------------------------------+

| YWJj | abc |

+------------------+-------------------------------+

1 row in set (0.00 sec)

HEX()：16進位制編碼
UNHEX()：16進位制解碼

mysql> SELECT 0x616263, HEX('abc'), UNHEX(HEX('abc'));
+----------+------------+-------------------+
| 0x616263 | HEX('abc') | UNHEX(HEX('abc')) |
+----------+------------+-------------------+
| abc | 616263 | abc |
+----------+------------+-------------------+
1 row in set (0.00 sec)

INSERT()：在指定位置替換字元/字串

mysql> SELECT INSERT('Quadratic',3,4,'What'),
-> INSERT('Quadratic',-1,4,'What'),
-> INSERT('Quadratic',3,100,'What');
+--------------------------------+---------------------------------+----------------------------------+
| INSERT('Quadratic',3,4,'What') | INSERT('Quadratic',-1,4,'What') | INSERT('Quadratic',3,100,'What') |
+--------------------------------+---------------------------------+----------------------------------+
| QuWhattic | Quadratic | QuWhat |
+--------------------------------+---------------------------------+----------------------------------+
1 row in set (0.00 sec)

INSTR()：返回字串的位置，類似POSITION()與LOCATION()

mysql> SELECT INSTR('mysql','ql');
+---------------------+
| INSTR('mysql','ql') |
+---------------------+
| 4 |
+---------------------+
1 row in set (0.00 sec)

LEFT()：返回字串左側的指定字元數的字串

mysql> SELECT LEFT('mysql',2);
+-----------------+
| LEFT('mysql',2) |
+-----------------+
| my |
+-----------------+
1 row in set (0.00 sec)

LENGTH()與OCTET_LENGTH()：返回字串的字元數

mysql> SELECT LENGTH('mysql'), LENGTH('嗯');
+-----------------+--------------+
| LENGTH('mysql') | LENGTH('嗯') |
+-----------------+--------------+
| 5 | 3 |
+-----------------+--------------+
1 row in set (0.00 sec)

LOCATE()與POSITION()：返回字串所在的位置，類似INSTR。

mysql> SELECT LOCATE('ql','mysql');
+----------------------+
| LOCATE('ql','mysql') |
+----------------------+
| 4 |
+----------------------+
1 row in set (0.00 sec)

LOWER()與LCASE()：返回所有字串的小寫形式

mysql> SELECT LOWER('MySQL');
+----------------+
| LOWER('MySQL') |
+----------------+
| mysql |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT LCASE('MYSQL');
+----------------+
| LCASE('MYSQL') |
+----------------+
| mysql |
+----------------+
1 row in set (0.00 sec)

LPAD()：返回指定長度的字串，在左側用指定字元填充。

mysql> SELECT LPAD('MySQL',10,'a');
+----------------------+
| LPAD('MySQL',10,'a') |
+----------------------+
| aaaaaMySQL |
+----------------------+
1 row in set (0.00 sec)

LTRIM()與RTRIM()：在左側或右側去除空格並返回字串，類似於TRIM()

mysql> SELECT LTRIM(' _mysql') a, RTRIM('mysql_ ') b;
+--------+--------+
| a | b |
+--------+--------+
| _mysql | mysql_ |
+--------+--------+
1 row in set (0.00 sec)

MAKE_SET()：按bit的排列方式，當位等於1時，返回該值，等於0時，不返回，從左向右排列字元。

mysql> SELECT MAKE_SET(2,'a','b','c','d'),
-> MAKE_SET(3,'a','b','c','d'),
-> MAKE_SET(4,'a','b','c','d'),
-> MAKE_SET(5,'a','b','c','d')\G
*************************** 1. row ***************************
MAKE_SET(2,'a','b','c','d'): b
MAKE_SET(3,'a','b','c','d'): a,b
MAKE_SET(4,'a','b','c','d'): c
MAKE_SET(5,'a','b','c','d'): a,c
1 row in set (0.00 sec)

OCT()：八進位制編碼

mysql> SELECT OCT(12);
+---------+
| OCT(12) |
+---------+
| 14 |
+---------+
1 row in set (0.00 sec)

ORD()：返回字串的第一個字元的ASCII碼

mysql> SELECT ORD('a'), ORD('all'), ASCII('a');
+----------+------------+------------+
| ORD('a') | ORD('all') | ASCII('a') |
+----------+------------+------------+
| 97 | 97 | 97 |
+----------+------------+------------+
1 row in set (0.00 sec)

QUOTE()：用單引號將整個字串包裹起來，併為字串本身的特殊字元增加轉義字元

mysql> SELECT QUOTE('Don\'t!'),
-> QUOTE('\t');
+------------------+-------------+
| QUOTE('Don\'t!') | QUOTE('\t') |
+------------------+-------------+
| 'Don\'t!' | ' ' |

REPEAT()：重複輸出字串

mysql> SELECT REPEAT('mysql',5);
+---------------------------+
| REPEAT('mysql',5) |
+---------------------------+
| mysqlmysqlmysqlmysqlmysql |
+---------------------------+
1 row in set (0.00 sec)

REPLACE()：將字串中的字元(串)替換成另一個字元(串)

mysql> SELECT REPLACE('www.google.com','com','cn');
+--------------------------------------+
| REPLACE('www.google.com','com','cn') |
+--------------------------------------+
| www.google.cn |
+--------------------------------------+
1 row in set (0.00 sec)

REVERSE()：返回字串以置返的形式（置返字串）

mysql> SELECT REVERSE('123456');
+-------------------+
| REVERSE('123456') |
+-------------------+
| 654321 |
+-------------------+
1 row in set (0.00 sec)

RIGHT()：返回字串右側的指定字元數的字串

mysql> SELECT RIGHT('mysql',3);
+------------------+
| RIGHT('mysql',3) |
+------------------+
| sql |
+------------------+
1 row in set (0.00 sec)

RPAD()：返回指定長度的字串，在右側用指定字元填充。

mysql> SELECT RPAD('MySQL',10,'_');
+----------------------+
| RPAD('MySQL',10,'_') |
+----------------------+
| MySQL_____ |
+----------------------+
1 row in set (0.00 sec)

SOUNDEX()：返回soundex字串（標準的為4字元），但是該函式可能返回一個稍長一點的字串，利用字元的讀音近似值所得。
目前是一個有限制的函式，只能用於英語，其他語言會產生無法預期的結果，官方表示將在未來版本（5.6以後）移除這些限制，可以在官方查詢BUG #22638來獲得更多資訊。

mysql> SELECT SOUNDEX('HELLO'), SOUNDEX('Quadratically');
+------------------+--------------------------+
| SOUNDEX('HELLO') | SOUNDEX('Quadratically') |
+------------------+--------------------------+
| H400 | Q36324 |
+------------------+--------------------------+
1 row in set (0.00 sec)

SPACE()：返回N個空格

mysql> SELECT SPACE(10) a, LENGTH(SPACE(10));
+------------+-------------------+
| a | LENGTH(SPACE(10)) |
+------------+-------------------+
| | 10 |
+------------+-------------------+
1 row in set (0.00 sec)

STRCMP()：返回字串的比較結果，0=same，-1=smaller，1=other（結果說明不分大小寫）

mysql> SELECT STRCMP('mysql','MYSQL'), STRCMP('mysql','mysql'), STRCMP('mysql','oracle'), STRCMP('oracle','mysql')\G
*************************** 1. row ***************************
STRCMP('mysql','MYSQL'): 0
STRCMP('mysql','mysql'): 0
STRCMP('mysql','oracle'): -1
STRCMP('oracle','mysql'): 1
1 row in set (0.00 sec)

SUBSTRING()與MID()與SUBSTR()：：返回從指定位置開始的字串

mysql> SELECT SUBSTRING(123456,3); -- 可以用於數字
+-----------------------+
| SUBSTRING(123456,'3') |
+-----------------------+
| 3456 |
+-----------------------+
1 row in set (0.00 sec)
mysql> SELECT SUBSTRING('ABCDEFG',3); -- 可以用於字串
+--------------------------+
| SUBSTRING('ABCDEFG','3') |
+--------------------------+
| CDEFG |
+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('ABCDEFG',3,2); -- 也可以指定返回幾個，比如從3號位置開始返回，返回2個字元
+--------------------------+
| SUBSTRING('ABCDEFG',3,2) |
+--------------------------+
| CD |
+--------------------------+
1 row in set (0.00 sec)

SUBSTRING_INDEX()：返回字串的部分，基於指定分隔符。（如果是正數就從左往右開始搜尋，反之亦然）

mysql> SELECT SUBSTRING_INDEX('abc@email.com','@',2); -- 以@為分隔符，返回第二個整部分的字串
+----------------------------------------+
| SUBSTRING_INDEX('abc@email.com','@',2) |
+----------------------------------------+
| abc@email.com |
+----------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT SUBSTRING_INDEX('abc@email.com','@',1); -- 以@為分隔符，返回第一部分的字串
+----------------------------------------+
| SUBSTRING_INDEX('abc@email.com','@',1) |
+----------------------------------------+
| abc |
+----------------------------------------+
1 row in set (0.00 sec)

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

TRIM()：移除字串的空格或指定字元

mysql> SELECT TRIM(' mysql ') as A; -- 預設移除空格
+-------+
| A |
+-------+
| mysql |
+-------+
1 row in set (0.00 sec)
mysql> SELECT TRIM(TRAILING 'asd' FROM 'mysqlasdasd') as A; -- TRAILING用來移除字串末尾的指定字元
+-------+
| A |
+-------+
| mysql |
+-------+
1 row in set (0.00 sec)
mysql> SELECT TRIM(LEADING 'asd' FROM 'asdmysql') as A; -- LEADING用來移除字串開始的指定字元
+-------+
| A |
+-------+
| mysql |
+-------+
1 row in set (0.00 sec)

UPPER()與UCASE()：返回所有字串的大寫形式

mysql> SELECT UPPER('MySQL');
+----------------+
| UPPER('MySQL') |
+----------------+
| MYSQL |
+----------------+
1 row in set (0.00 sec)

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

作者公眾號（持續更新）

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

MySQL 字串函式大全

相關文章