为了演示以下的文本操作函数，首先创建一个names表格。
mysql> SELECT * FROM names;
+------+
| name |
+------+
| 李四 |
| Amy |
| 123 |
+------+
LENGTH
返回字符串的长度。
注意：
-
utf8编码下,一个汉字算三个字符,一个数字或字母算一个字符。 - 其他编码下,一个汉字算两个字符, 一个数字或字母算一个字符。
utf8编码示例：
mysql> SELECT LENGTH(name) FROM names;
+--------------+
| LENGTH(name) |
+--------------+
| 6 |
| 3 |
| 3 |
+--------------+
其他编码示例(我的数据库的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
如果我们想得到一个字符串它的“位数”长度，就可以使用CHAR_LENGTH函数，无论是一个汉字，一个英文还是一个数字，都看作一个字符，这可能也更符合很多时候我们队字符串长度的获取需求。
示例：
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)从字符串左边开始取指定长度的子字符串并返回；
RIGHT(str, length)从字符串右边开始取指定长度的子字符串并返回。
示例：
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)将字符串转为小写并返回；
UPPER(str)将字符串转为大写并返回。
示例（中文和数字不变）：
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位字符
- 字符位数从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,...)连接多个字符串
- 若有字符串为
NULL，则返回NULL
CONCAT_WS(separator,str1,str2,…)以分隔符连接多个字符串
- 若
separator为NULL,则结果为NULL； - 字符串中为
NULL的，连接时会被忽略。
示例：
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首次出现的位置，找不到则返回0。注意二者的参数列表是反的。
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,并返回找到的第一个substr的位置。
mysql> SELECT LOCATE('L','HELLO',4);
+-----------------------+
| LOCATE('L','HELLO',4) |
+-----------------------+
| 4 |
+-----------------------+
1 row in set (0.00 sec)
网友评论