Mysql 字符串函数(1)

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)