一、应用场景
假设数据库有这样的数据:
1_1_xiao_ming
2_22_zhang_mou_mou
3_333_qian_x_s
现在需要把第2个下划线后的数据查询出来;
如果只用substring_index
,较难实现;
此时可以联合使用locate
与substring
实现,样例sql如下:
select
substring(test,LOCATE('_',test,3)+1),
substring_index(test,'_',-2)
from ttt;
这里做了下对比,可以看到,如果只用substring_index
,由于无法确定后面有多少个_
,所以不太行,查到的姓名不全;
而联合使用locate
与substring
,就可以实现需求,把第2个下划线后的数据查询出来。
二、详细说明
1.locate用法
样例1:
LOCATE('_',test)
这个意思是,返回test字段中、首次出现的’_'的位置下标。
样例2:
LOCATE('_',test,3)
这个意思是,返回test字段中、从第3个位置以后的、第一个出现的’_'的位置下标。
上方样例中,第一个'_'出现的位置一般是第2位,但是第二个'_'出现的位置就不固定了,所以需要用locate,找到第二个'_'出现的位置。
2.substring用法
样例1:
substring(test,LOCATE('_',test,3)+1)
这个意思是,返回test字段中、从第3个位置后出现的第一个'_'的位置+1
、到最后为止的内容。
上方样例中,就是用这个查询到第2个下划线后的数据的。
样例2:
substring(test,3,1)
这个意思是,返回test字段中、从第3个位置开始、长度为1的内容。
3.substring_index用法
样例1
substring_index(test,'_',-2)
这个的意思是,返回test字段中、按照'_'
分隔、倒数第2个'_'
开始、到末尾的内容。
上方样例中,倒数第2个下划线开始到末尾的内容,不能符合要求。
样例2
substring_index(test,'_',2)
这个的意思是,返回test字段中、按照'_'
分隔、从开始到正数第2个'_'
之间的内容。
上方样例中,这样也不符合要求。