联系：手机/微信(+86 17813235971) QQ(107644445)
标题：mysql通过substring_index和substring截取字符串
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
今天朋友问我mysql中有个列如下数值，怎么提出出其中ip地址
BGP-BeiJing-59.151.105.130-AD_read-YeZongKun
BGP-BeiJing-59.151.105.138_140_141-AD_LVS-YeZongKun
我提供sql语句如下：
select SUBSTRING(substring_index(‘BGP-BeiJing-59.151.105.130-AD_read-YeZongKun’,’-‘,3),(length(substring_index(‘BGP-BeiJing-59.151.105.130-AD_read-YeZongKun’,’-‘,2))+2));
SUBSTRING
SUBSTRING(str,pos,len)
SUBSTRING(str FROM pos FOR len)
SUBSTRING(str,pos)
SUBSTRING(str FROM pos)
mysql> SELECT SUBSTRING(‘Quadratically’,5);
-> ‘ratically’
mysql> SELECT SUBSTRING(‘foobarbar’ FROM 4);
-> ‘barbar’
mysql> SELECT SUBSTRING(‘Quadratically’,5,6);
-> ‘ratica’
mysql> SELECT SUBSTRING(‘Sakila’, -3);
-> ‘ila’
mysql> SELECT SUBSTRING(‘Sakila’, -5, 3);
-> ‘aki’
mysql> SELECT SUBSTRING(‘Sakila’ FROM -4 FOR 2);
-> ‘ki’
substring_index
substring_index(str,delim,count)
mysql> SELECT SUBSTRING_INDEX(‘www.mysql.com’, ‘.’, 2);
-> ‘www.mysql’
mysql> SELECT SUBSTRING_INDEX(‘www.mysql.com’, ‘.’, -2);
-> ‘mysql.com’