MySQL文本操作函數(shù)

為了演示以下的文本操作函數(shù)，首先創(chuàng)建一個(gè)names表格。

mysql> SELECT * FROM names;
+------+
| name |
+------+
| 李四 |
| Amy  |
| 123  |
+------+

LENGTH

返回字符串的長(zhǎng)度。
注意：

  • utf8編碼下,一個(gè)漢字算三個(gè)字符,一個(gè)數(shù)字或字母算一個(gè)字符。
  • 其他編碼下,一個(gè)漢字算兩個(gè)字符, 一個(gè)數(shù)字或字母算一個(gè)字符。

utf8編碼示例：

mysql> SELECT LENGTH(name) FROM names;
+--------------+
| LENGTH(name) |
+--------------+
|            6 |
|            3 |
|            3 |
+--------------+

其他編碼示例(我的數(shù)據(jù)庫(kù)的character_set_connection字符集為gbk)：

mysql> SELECT LENGTH('李四');
+----------------+
| LENGTH('李四') |
+----------------+
|              4 |
+----------------+
1 row in set (0.00 sec)

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

mysql> SELECT LENGTH(123);
+-------------+
| LENGTH(123) |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)

CHAR_LENGTH

如果我們想得到一個(gè)字符串它的“位數(shù)”長(zhǎng)度，就可以使用CHAR_LENGTH函數(shù)，無(wú)論是一個(gè)漢字，一個(gè)英文還是一個(gè)數(shù)字，都看作一個(gè)字符，這可能也更符合很多時(shí)候我們隊(duì)字符串長(zhǎng)度的獲取需求。
示例：

mysql> SELECT CHAR_LENGTH(name) FROM names;
+-------------------+
| CHAR_LENGTH(name) |
+-------------------+
|                 2 |
|                 3 |
|                 3 |
+-------------------+
3 rows in set (0.00 sec)

mysql> SELECT CHAR_LENGTH('李四');
+---------------------+
| CHAR_LENGTH('李四') |
+---------------------+
|                   2 |
+---------------------+
1 row in set (0.00 sec)

LEFT, RIGHT

LEFT(str, length)從字符串左邊開始取指定長(zhǎng)度的子字符串并返回；
RIGHT(str, length)從字符串右邊開始取指定長(zhǎng)度的子字符串并返回。
示例：

mysql> SELECT LEFT(name,1) FROM names;
+--------------+
| LEFT(name,1) |
+--------------+
| 李           |
| A            |
| 1            |
+--------------+
3 rows in set (0.02 sec)

mysql> SELECT RIGHT(name,1) FROM names;
+---------------+
| RIGHT(name,1) |
+---------------+
| 四            |
| y             |
| 3             |
+---------------+
3 rows in set (0.00 sec)

LOWER, UPPER

LOWER(str)將字符串轉(zhuǎn)為小寫并返回；
UPPER(str)將字符串轉(zhuǎn)為大寫并返回。
示例（中文和數(shù)字不變）：

mysql> SELECT LOWER(name) FROM names;
+-------------+
| LOWER(name) |
+-------------+
| 李四        |
| amy         |
| 123         |
+-------------+
3 rows in set (0.03 sec)

mysql> SELECT UPPER(name) FROM names;
+-------------+
| UPPER(name) |
+-------------+
| 李四        |
| AMY         |
| 123         |
+-------------+
3 rows in set (0.03 sec)

LTRIM, RTRIM, TRIM

LTRIM(str)將字符串左邊空格去除后返回；
RTRIM(str)將字符串右邊空格去除后返回；
TRIM(str)將字符串左右兩邊空格去除后返回。
示例：

mysql> SELECT LTRIM('  really good day  ');
+------------------------------+
| LTRIM('  really good day  ') |
+------------------------------+
| really good day              |
+------------------------------+
1 row in set (0.00 sec)

mysql> SELECT RTRIM('  really good day  ');
+------------------------------+
| RTRIM('  really good day  ') |
+------------------------------+
|   really good day            |
+------------------------------+
1 row in set (0.02 sec)

mysql> SELECT TRIM('  really good day  ');
+-----------------------------+
| TRIM('  really good day  ') |
+-----------------------------+
| really good day             |
+-----------------------------+
1 row in set (0.00 sec)

SUBSTRING

SUBSTRING(str,m,n)從字符串第m位開始截取n位字符

  • 字符位數(shù)從1開始
  • 若n缺省，則截取至字符串末位

示例：

mysql> SELECT SUBSTRING('really good day',0,1);
+----------------------------------+
| SUBSTRING('really good day',0,1) |
+----------------------------------+
|                                  |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('really good day',1,1);
+----------------------------------+
| SUBSTRING('really good day',1,1) |
+----------------------------------+
| r                                |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('really good day',8,4);
+----------------------------------+
| SUBSTRING('really good day',8,4) |
+----------------------------------+
| good                             |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('really good day',8);
+--------------------------------+
| SUBSTRING('really good day',8) |
+--------------------------------+
| good day                       |
+--------------------------------+
1 row in set (0.00 sec)

CONCAT, CONCAT_WS

CONCAT(str1,str2,...)連接多個(gè)字符串

  • 若有字符串為NULL，則返回NULL

CONCAT_WS(separator,str1,str2,…)以分隔符連接多個(gè)字符串

  • 若separator為NULL,則結(jié)果為NULL；
  • 字符串中為NULL的，連接時(shí)會(huì)被忽略。

示例：

CONCAT

mysql> SELECT CONCAT('really','Good','Day');
+-------------------------------+
| CONCAT('really','Good','Day') |
+-------------------------------+
| reallyGoodDay                 |
+-------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CONCAT('really','Good',NULL,'Day');
+------------------------------------+
| CONCAT('really','Good',NULL,'Day') |
+------------------------------------+
| NULL                               |
+------------------------------------+
1 row in set (0.00 sec)

CONCAT_WS

mysql> SELECT CONCAT_WS('~','really','good','day');
+--------------------------------------+
| CONCAT_WS('~','really','good','day') |
+--------------------------------------+
| really~good~day                      |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CONCAT_WS('~','really','good',NULL,'day');
+-------------------------------------------+
| CONCAT_WS('~','really','good',NULL,'day') |
+-------------------------------------------+
| really~good~day                           |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CONCAT_WS(NULL,'really','good',NULL,'day');
+--------------------------------------------+
| CONCAT_WS(NULL,'really','good',NULL,'day') |
+--------------------------------------------+
| NULL                                       |
+--------------------------------------------+
1 row in set (0.00 sec)

INSTR, LOCATE

INSERT(str,substr)和LOCATE(substr,str)均返回str字符串中substr首次出現(xiàn)的位置，找不到則返回0。注意二者的參數(shù)列表是反的。

mysql> SELECT LOCATE('L','HELLO');
+---------------------+
| LOCATE('L','HELLO') |
+---------------------+
|                   3 |
+---------------------+
1 row in set (0.05 sec)

mysql> SELECT INSTR('HELLO','L');
+--------------------+
| INSTR('HELLO','L') |
+--------------------+
|                  3 |
+--------------------+

mysql> SELECT LOCATE('A','HELLO');
+---------------------+
| LOCATE('A','HELLO') |
+---------------------+
|                   0 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT INSTR('HELLO','A');
+--------------------+
| INSTR('HELLO','A') |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.00 sec)

另外，LOCATE(substr,str,pos)可以從pos處開始查找substr,并返回找到的第一個(gè)substr的位置。

mysql> SELECT LOCATE('L','HELLO',4);
+-----------------------+
| LOCATE('L','HELLO',4) |
+-----------------------+
|                     4 |
+-----------------------+
1 row in set (0.00 sec)
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容