字符串截取与替换函数

1、从左边或右边截取子字符串

left(str,num);

right(str,num);

返回字符串str中包含前num个字母（从左/或右边数）的字符串。

mysql> select left('mysql',2),right('mysql',3);
+-----------------+------------------+
| left('mysql',2) | right('mysql',3) |
+-----------------+------------------+
| my              | sql              |
+-----------------+------------------+
1 row in set (0.00 sec)

2、截取指定位置和长度的字符串

substring(str,num,len);

mid(str,num,len);

返回字符串str中的第num个位置开始长度为len的子字符串。

mysql> select substring('zhaojd',2,3);
+-------------------------+
| substring('zhaojd',2,3) |
+-------------------------+
| hao |
+-------------------------+
1 row in set (0.00 sec)
mysql> select mid('zhapjd',2,3);
+-------------------+
| mid('zhapjd',2,3) |
+-------------------+
| hap |
+-------------------+
1 row in set (0.00 sec)
mysql> select substring('zhaojd',2,3),mid('zhaojd',2,3);
+-------------------------+-------------------+
| substring('zhaojd',2,3) | mid('zhaojd',2,3) |
+-------------------------+-------------------+
| hao | hao |
+-------------------------+-------------------+
1 row in set (0.00 sec)

3、替换字符串

replace(str,substr,newstr);

将字符串str中的子字符串substr用字符串newstr来替换。

mysql> select replace('这是MySQL数据库','MySQL','db2');
+-----------------------------------------------+
| replace('这是MySQL数据库','MySQL','db2') |
+-----------------------------------------------+
| 这是db2数据库 |
+-----------------------------------------------+
1 row in set (0.00 sec)