mysql语句locate与substring联合使用方法

我会带着你远行 2024-04-03 07:25 14阅读 0赞

## 一、应用场景 ##

假设数据库有这样的数据：

1_1_xiao_ming
    2_22_zhang_mou_mou
    3_333_qian_x_s

![在这里插入图片描述][baf8cfa4ba77454b8f41bfd5989f0632.png]

现在需要把第2个下划线后的数据查询出来；  
如果只用`substring_index`，较难实现；  
此时可以联合使用`locate`与`substring`实现，样例sql如下：

select
    
    substring(test,LOCATE('_',test,3)+1),
    substring_index(test,'_',-2)
    
    from ttt;

![在这里插入图片描述][f60e0dc5a8ca4704b39409ec0a8fb39c.png]

这里做了下对比，可以看到，如果只用`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个`'_'`之间的内容。  
`上方样例中，这样也不符合要求。`

[baf8cfa4ba77454b8f41bfd5989f0632.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/cd680ff0b6e84142a006a05e0b0f3445.png
[f60e0dc5a8ca4704b39409ec0a8fb39c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/8a3afb131bce4d0db65a9f16f0528324.png