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开始后的一个字符串