mysql 字符串 1_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)

分享到：

2011-10-14 13:21

浏览 792

评论