substr mysql_MySQL数据库中substr字符串分隔函数简介说明

摘要:

下文讲述MySQL数据库中substr字符串分隔函数的用法说明，如下所示；

substr字符串分隔函数功能:

substr系统函数的功能:

对字符串进行分隔，并返回分隔后的字符串

substr字符串分隔函数的四种语法形式:

str:待分隔字符串

pos:起始位置

len:截取长度

substr(str,pos)

从pos开始截取字符串,直到结尾

substr(str,pos,len)

从pos开始截取字符串,

共截取len位

substr(str from pos)

同substr(str,pos)相同

substr(str from pos for len)

同substr(str,pos,len)相同

注意事项:

1.pos值不可以为零,pos为0时，返回空字符串

2.len值小于等于0时，则返回空字符串

3.pos为负数时，则从右边数字符串的开始位置

例:

mysql> select substr('maomao365.com',0);

+---------------------------+

| substr('maomao365.com',0) |

+---------------------------+

| |

+---------------------------+

1 row in set (0.00 sec)

mysql> select substr('maomao365.com',1);

+---------------------------+

| substr('maomao365.com',1) |

+---------------------------+

| maomao365.com |

+---------------------------+

1 row in set (0.00 sec)

mysql> select substr('maomao365.com',2);

+---------------------------+

| substr('maomao365.com',2) |

+---------------------------+

| aomao365.com |

+---------------------------+

1 row in set (0.00 sec)

mysql> select substr('maomao365.com',-1);

+----------------------------+

| substr('maomao365.com',-1) |

+----------------------------+

| m |

+----------------------------+

1 row in set (0.00 sec)

mysql> select substr('maomao365.com',-2);

+----------------------------+

| substr('maomao365.com',-2) |

+----------------------------+

| om |

+----------------------------+

1 row in set (0.00 sec)

mysql> select substr('maomao365.com',-2,10);

+-------------------------------+

| substr('maomao365.com',-2,10) |

+-------------------------------+

| om |

+-------------------------------+

1 row in set (0.00 sec)

mysql> select substr('maomao365.com',2,10);

+------------------------------+

| substr('maomao365.com',2,10) |

+------------------------------+

| aomao365.c |

+------------------------------+

1 row in set (0.00 sec)

mysql> select substr('maomao365.com',0,10);

+------------------------------+

| substr('maomao365.com',0,10) |

+------------------------------+

| |

+------------------------------+

1 row in set (0.00 sec)

mysql> select substr('maomao365.com' from 3);

+--------------------------------+

| substr('maomao365.com' from 3) |

+--------------------------------+

| omao365.com |

+--------------------------------+

1 row in set (0.00 sec)

mysql> select substr('maomao365.com' from 3 for 10);

+---------------------------------------+

| substr('maomao365.com' from 3 for 10) |

+---------------------------------------+

| omao365.co |

+---------------------------------------+

1 row in set (0.00 sec)

mysql> select substr('maomao365.com' from 3 for -1);

+---------------------------------------+

| substr('maomao365.com' from 3 for -1) |

+---------------------------------------+

| |

+---------------------------------------+

1 row in set (0.00 sec)

mysql> select substr('maomao365.com' from 3 for 0);

+--------------------------------------+

| substr('maomao365.com' from 3 for 0) |

+--------------------------------------+

| |

+--------------------------------------+

1 row in set (0.00 sec)