1.left(str,length) 字符串截取函数
mysql> select left('string',2);
+------------------+
| left('string',2) |
+------------------+
| st |
+------------------+
1 row in set (0.00 sec)
2.right(str,length) 字符串截取函数
mysql> select right('string',3);
+-------------------+
| right('string',3) |
+-------------------+
| ing |
+-------------------+
1 row in set (0.00 sec)
3.substring(str,pos) 字符串截取函数
mysql> select substring('string',2);/*截取左边第二个字符到最后*/
+-----------------------+
| substring('string',2) |
+-----------------------+
| tring |
+-----------------------+
1 row in set (0.00 sec)
mysql> select substring('string',-4);/*从后往前截取4个字符*/
+------------------------+
| substring('string',-4) |
+------------------------+
| ring |
+------------------------+
1 row in set (0.00 sec)
4.substring(str,begin_pos,length);字符串截取
mysql> select substring('string',3,2);/*从左边第三个字符开始,截取两个字符*/
+-------------------------+
| substring('string',3,2) |
+-------------------------+
| ri |
+-------------------------+
1 row in set (0.00 sec)
mysql> select substring('string',-6,2);/*从前向后倒数第6个字符开始向后去2个字符*/
+--------------------------+
| substring('string',-6,2) |
+--------------------------+
| st |
+--------------------------+
1 row in set (0.00 sec)
注:substring函数中的第二个参数pos可以为负数,表示从后向前算,但是length不能为负,为负时返回为空串
5.substring_index(str,char,count)
mysql> select substring_index('www.csdn.net','.',2);/*截取从开始到第二个‘.’出的字符串*/
+---------------------------------------+
| substring_index('www.csdn.net','.',2) |
+---------------------------------------+
| www.csdn |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> select substring_index('www.csdn.net','.',-2);/*截取倒数第二个‘.’到最后的字符串*/
+----------------------------------------+
| substring_index('www.csdn.net','.',-2) |
+----------------------------------------+
| csdn.net |
+----------------------------------------+
1 row in set (0.00 sec)
mysql> select substring_index('www.csdn.net','a',-2);/*如果所截取字符串中没有所要匹配的字符,则返回整个字符串*/
+----------------------------------------+
| substring_index('www.csdn.net','a',-2) |
+----------------------------------------+
| www.csdn.net |
+----------------------------------------+
1 row in set (0.00 sec)