SUBSTRING_INDEX(str,delim,count)
count
为正数,则返回最后一个分隔符出现位置左侧的所有字符;如果count
为负数,则返回最后一个分隔符出现位置右侧的所有字符。应用示例:
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
-> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
-> 'mysql.com'
使用得当可以实现split拆分字段功能。 假设有表内容如下:
mysql> select * from a;
+--------------------------+
| login_info |
+--------------------------+
| 10.23.1.8&root&pwd123456 |
+--------------------------+
1 row in set (0.00 sec)
该字段由3部分组成,分别对应ip, user, password
,现在要使用sql将该字段拆分,代码如下:
mysql> select
-> `login_info`,
-> substring_index(`login_info`, "&", 1) as `ip`,
-> substring_index(substring_index(`login_info`,"&",-2),"&",1) as `user`,
-> substring_index(`login_info`,"&",-1) as `password`
-> from a;
+--------------------------+-----------+------+-----------+
| login_info | ip | user | password |
+--------------------------+-----------+------+-----------+
| 10.23.1.8&root&pwd123456 | 10.23.1.8 | root | pwd123456 |
+--------------------------+-----------+------+-----------+
1 row in set (0.00 sec)
是不是有点意思?