mysql---字符串函数

最新推荐文章于 2024-11-17 15:55:19 发布

原创最新推荐文章于 2024-11-17 15:55:19 发布 · 393 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#select

数据库专栏收录该内容

15 篇文章

订阅专栏

本文介绍了MySQL中常用的字符串处理函数，包括拼接(concat)、插入(insert)、大小写转换(lower、upper)、截取(left、right)、填充(lpad、rpad)、去除空白(ltrim、rtrim)、重复(repeat)、替换(replace)、比较(strcmp)、截取(substring)等，并通过实例展示了这些函数的具体应用。

1,concat

mysql> select concat('aaa', 'bbb', 'cccc'), concat('aaa', null);
+------------------------------+---------------------+
| concat('aaa', 'bbb', 'cccc') | concat('aaa', null) |
+------------------------------+---------------------+
| aaabbbcccc                   | NULL                |
+------------------------------+---------------------+
1 row in set (0.01 sec)

含有null的拼接返回null，否则直接拼接
2,insert

mysql> select insert('beijing2008', 8, 4, 'me');
+-----------------------------------+
| insert('beijing2008', 8, 4, 'me') |
+-----------------------------------+
| beijingme                         |
+-----------------------------------+
1 row in set (0.00 sec)

将beijing2008字符串从第八位后的四位字符串替换成me；
3，lower upper

mysql> select lower('Aaaaa');
+----------------+
| lower('Aaaaa') |
+----------------+
| aaaaa          |
+----------------+
1 row in set (0.00 sec)

mysql> select uper('Abplc');
ERROR 1305 (42000): FUNCTION user.uper does not exist
mysql> select upper('Abdasf');
+-----------------+
| upper('Abdasf') |
+-----------------+
| ABDASF          |
+-----------------+
1 row in set (0.00 sec)

lower 全部转成小写，upper 全部转成大写

4,left right

mysql> select left('beijing2008', 4), left('beijing2008', null), right('beijing2008', 4);
+------------------------+---------------------------+-------------------------+
| left('beijing2008', 4) | left('beijing2008', null) | right('beijing2008', 4) |
+------------------------+---------------------------+-------------------------+
| beij                   | NULL                      | 2008                    |
+------------------------+---------------------------+-------------------------+
1 row in set (0.00 sec)

left 显示字符串左边指定长度字符串， right显示字符串右边指定长度字符串
5，lpad， rpad

mysql> select lpad('2008',30,'beijing'), rpad('beijing',20,'2008');
+--------------------------------+---------------------------+
| lpad('2008',30,'beijing')      | rpad('beijing',20,'2008') |
+--------------------------------+---------------------------+
| beijingbeijingbeijingbeiji2008 | beijing2008200820082      |
+--------------------------------+---------------------------+
1 row in set (0.00 sec)

用beijing对2008左填充直到字符串长度为30，用2008对beijing右填充直到字符串长度为20；
6,ltrim,rtrim

mysql> select ltrim(' beijing | '), rtrim(' beijing | ');
+----------------------+----------------------+
| ltrim(' beijing | ') | rtrim(' beijing | ') |
+----------------------+----------------------+
| beijing |            |  beijing |           |
+----------------------+----------------------+
1 row in set (0.00 sec)

对beijing进行左侧去空格和右侧去空格
7,repeat

mysql> select repeat('beijing', 3);
+-----------------------+
| repeat('beijing', 3)  |
+-----------------------+
| beijingbeijingbeijing |
+-----------------------+
1 row in set (0.00 sec)

对beijing进行三次重复
8,replace

mysql> select replace('beijing_2010_2010', '2010', '2008');
+----------------------------------------------+
| replace('beijing_2010_2010', '2010', '2008') |
+----------------------------------------------+
| beijing_2008_2008                            |
+----------------------------------------------+
1 row in set (0.01 sec)

将所有的2010全部替换成2008
9,strcmp

mysql> select strcmp('a', 'b'), strcmp('b', 'a');
+------------------+------------------+
| strcmp('a', 'b') | strcmp('b', 'a') |
+------------------+------------------+
|               -1 |                1 |
+------------------+------------------+
1 row in set (0.00 sec)

用来比较字符串的ASSCII码大小，前者小于后者返回-1，相等返回0，大于后者返回1
10,trim

mysql> select trim(' | beijing2008 |  ');
+----------------------------+
| trim(' | beijing2008 |  ') |
+----------------------------+
| | beijing2008 |            |
+----------------------------+
1 row in set (0.00 sec)

去掉字符串开头和结尾的空格
11,substring

mysql> select substring('beijing', 3, 8), substring('beijing', 3, 1);
+----------------------------+----------------------------+
| substring('beijing', 3, 8) | substring('beijing', 3, 1) |
+----------------------------+----------------------------+
| ijing                      | i                          |
+----------------------------+----------------------------+
1 row in set (0.00 sec)

提取beijing从3开始后的8个字符串和从3开始后的一个字符串