MySQL中SUBSTRING_INDEX函数的使用&Oracle中的替代方案

SUBSTRING_INDEX函数简介

SUBSTRING_INDEX是MySQL中一个很实用的字符串处理函数，它的格式如下所示

SUBSTRING_INDEX('待处理字符串', '分隔符', 'count')

使用SUBSTRING_INDEX可以截取到第count个分隔符的位置之前的子字符串。count指定的是第几个分隔符，如果count是正数则从左往右第count个分隔符位置，如果为负数则从右往左计数。下面是两个例子

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
 -> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
 -> 'mysql.com'

可以使用嵌套的SUBSTRING_INDEX获取某两个分隔符之间的内容

mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('www.mysql.com', '.', 2), '.', -1);
 -> 'mysql'

mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('www.mysql.com', '.', -2), '.', 1);
 -> 'mysql'

Oracle中的替代方案

SUBSTRING_INDEX并不是SQL标准函数，Oracle中并没有这个函数。Oracle当然也有替代的方案，只是稍微有点曲折。

SUBSTRING_INDEX在Oracle的一个等价实现

Oracle中可以借助两个函数

-- 返回字符串匹配位置的下标（从开始），索引表示第几个匹配，开始匹配位置如果是负数表明从右往左匹配
INSTR('字符串', '匹配字符串'[, 开始匹配位置[, 索引]])

select INSTR('www.mysql.com', '.') from dual;
4
-- 从右往左匹配
select INSTR('www.mysql.com', '.', -1) from dual;
10
-- 从左往右的第二个匹配位置
select INSTR('www.mysql.com', '.', 1, 2) from dual;
10

-- 从第5个字符开始匹配
select INSTR('www.mysql.com', '.', 5) from dual;
10

-- 子字符串函数，长度可以不指定，不指定则截取从开始位置到字符串结束
SUBSTR('字符串', 开始位置[, 长度])

使用上面两个函数可以实现MySQL中SUBSTRING_INDEX相同的效果

SUBSTRING_INDEX('字符串', '分隔符', count)

-- count为负数，即从右往左数第count个分隔符位置之后的字符
SUBSTR('字符串', INSTR('字符串', '分隔符', -1, |count|) + 1)

-- count为正数，即取第count个分隔符位置之前的字符
SUBSTR('字符串', 1, INSTR('字符串', '分隔符', 1, count) - 1)

实际可以根据INSTR和SUBSTR的功能灵活使用。比如取形如www.mysql.com这种三段式域名中间的部分可以如下分析

• 使用SUBSTR函数写出框架：SUBSTR('www.mysql.com', 开始位置，长度)
• 确定开始位置：本例中为第一个分隔符位置+1，即INSTR('www.mysql.com', '.', 1, 1) + 1
• 确定长度：由于中间位置长度不固定，可以使用第二个分隔符位置减去开始位置得到，即INSTR('www.mysql.com', '.', 1, 2) - (INSTR('www.mysql.com', '.', 1, 1) + 1)
• 最后可得到如下语句
  SELECT SUBSTR('www.mysql.com',INSTR('www.mysql.com', '.', 1, 1) + 1, INSTR('www.mysql.com', '.', 1, 2) - INSTR('www.mysql.com', '.', 1, 1) - 1) FROM DUAL;

参考资料

SUBSTRING_INDEX
SUBSTR函数
INSTR函数
how to split the value in oracle