oralce函数substr和mysql函数substring_index⼩记
最近偶尔在mysql数据库中看到了substring_index函数，先简单介绍下mysql的substring_index函数。
substring_index(str,delim,count) 返回字符串str中在第 count 个出现的分隔符 delim之前的⼦串，和java中的str.substring函数类似。
如果count是正数时候，substring_index是从左到右的顺序检查分隔符delim所在的位置。
如果count是负数时候，substring_index是从右到左的顺序检查分隔符delim所在的位置，返回的是从字符串str左边下标1开始的下标位置。
特殊点如果count等于0，那么，不管delim是否在str中存在，你得到的将是⼀个空⽩字符串。如下所⽰
select substring_index('www.baidu', '.', 0);
select substring_index('www.baidu', '-', 0);
上⾯说的很抽象，来个简单的例⼦。
select substring_index('www.baidu', '.', 2);
2为正数，.在str中存在，返回的结果是从下标1到第2次.出现的位置之间substr(1,10-1)的⼦串。所以结果是'www.baidu'。
select substring_index('www.baidu', '.', -2);
-2为负数，.在str中存在,.在str中搜索顺序是从右到左第2次出现的位置是4，⽤于是负数返回的是从最后到4+1的⼦串，也就是
substr(str,4+1)，所以结果是baidu。
可以看到substring_index返回的⼦串是不包含分隔符 delim在str中第count次匹配的位置的，如果分隔符delim在str中出现的位置⼩于count次会发⽣什么呢？来测试下：
select substring_index('www.baidu', '.', 3);
select substring_index('www.baidu', '.', -3);
运⾏之后可以看到返回的结果都是'www.baidu'。再来测试delim在str中不存在的情况：
select substring_index('www.baidu', '-', -1);
select substring_index('www.baidu', '-',1);
运⾏之后可以看到返回的结果都是'www.baidu'，从上⾯的测试可以知道分隔符delim在str中不存在或者出现次数⼩于指定的count时将返回str本⾝。上⾯只是测试了英⽂数据，中⽂数据测试也是⼀样的，测试如下：
select substring_index('我是中⽂_测试_数据','_',2) from dual;--我是中⽂_测试
select substring_index('我是中⽂_测试_数据','_',-2) from dual;--测试_数据
select substring_index('我是中⽂_测试_数据','_',0) from dual;--空⽩字符串
select substring_index('我是中⽂_测试_数据','_',3) from dual;--我是中⽂_测试_数据
中英⽂混合的情况我就不测试了。
mysql有substring_index函数，oracle是否也要类似的函数呢？
我所知道的oracle中常见的只有substr和mysql的substring_index函数类似，有⽹友知道oralce其他函数的，⿇烦留⾔告知。mysql中也有substr，关于两者的区别，后⾯我会简述。
先简单的介绍下oracle的substr函数和instr函数，后⾯我会⽤这2个函数模拟mysql的substring_index函数
instr( string1, string2 [, start_position [, nth_appearance ] ] )
string1 源字符串
string2 ⽬标字符串.
start_position 从string1 的哪个位置开始查。默认为1. 字符串索引从1开始。为正，从左到右开始检索，为负，从右到左检索。
nth_appearance 代表要查第⼏次出现的string2. 此参数可选，默认为 1.不能为负数
返回要查的字符串string2在源字符串string1中的符合条件的开始索引
substr(string,start_position,length)
string 源字符串，即被截取的字符串.
start_position 字符截取的开始位置.start_position⼤于0时，从左边算起,⼩于0时，从右边查起
length 截取字符的个数.默认截取到最后⼀位.
先来个测试：
select instr('www.baidu', '.',-1,2) from dual
.在str中存在,-1说明检索是从最后⼀位开始,2说明出现2次，返回的结果是4
select instr('www.baidu', '.',1,2) from dual
.在str中存在,1说明按从左到右的顺序，2说明出现2次，返回的结果是10
下⾯来点特殊的测试，先测试nth_appearance，不是说不能为负数吗，我测试nth_appearance=0看下
select instr('www.baidu', '.',1,0) from dual
select instr('www.baidu', '.',-1,0) from dual
你们猜结果是什么？结果是ORA-01428：参数'0'超出范围，说明nth_appearance不能为0，只能⼤于0。
再测试start_position，令start_position=0看下
select instr('www.baidu', '.',0,1) from dual
select instr('www.baidu', '-',0,1) from dual
select instr('www.baidu', '.',0,2) from dual
select instr('www.baidu', '-',0,2) from dual
测试结果都是0；如果把start_position改为>0，结果如下
select instr('www.baidu', '.',1,1) from dual--4
select instr('www.baidu', '-',1,1) from dual--0
select instr('www.baidu', '.',1,2) from dual--10
select instr('www.baidu', '-',1,2) from dual--0
如果把start_position改为<0，结果如下：
select instr('www.baidu', '.',-1,1) from dual--10
select instr('www.baidu', '-',-1,1) from dual--0
select instr('www.baidu', '.',-1,2) from dual--4
select instr('www.baidu', '-',-1,2) from dual--0
select instr('www.baidu', '.',-1,3) from dual--0
select instr('www.baidu', '-',-1,3) from dual--0
select instr('www.baidu', '.',-1,3) from dual--0
select instr('www.baidu', '-',-1,3) from dual--0
可以看出instr函数⽬标字符串在str中不存在时候或者出现次数⼩于给定的次数时，返回的都是0；
使⽤中⽂测试也是⼀样：
select instr('我是中⽂_测试_数据','_',1,0) from dual;--ORA-01428：参数'0'超出范围
select instr('我是中⽂_测试_数据','_',1,2) from dual;--8
select instr('我是中⽂_测试_数据','_',1,3) from dual;--0
select instr('我是中⽂_测试_数据','_',-1,2) from dual;--5
select instr('我是中⽂_测试_数据','_',-1,3) from dual;--0
下⾯测试下substr。
select substr('www.baidu',0,4) from dual
select substr('www.baidu',1,4) from dual
上⾯2个执⾏结果都是'www.'。如果上⾯是在mysql下测试，结果如下：
select substr('www.baidu',0,4) from dual--空⽩字符串
select substr('www.baidu',1,4) from dual--www.
从这⾥可以看出mysql中substr开始位置不能为0，⽽oracle下从0开始或者从1开始结果是⼀样的，下⾯继续oracle测试。
select substr('www.baidu',1) from dual
select substr('www.baidu',1,22) from dual
返回结果是'www.baidu'。
select substr('www.baidu',-1) from dual
select substr('www.baidu',-1,4) from dual
select substr('www.baidu',-1,22) from dual
返回的结果是'm'，说明substr返回的是从开始位置到str最后⼀位。
使⽤中⽂测试如下：
select substr('我是中⽂_测试_数据',1) from dual--我是中⽂_测试_数据
select substr('我是中⽂_测试_数据',1,22) from dual--我是中⽂_测试_数据
select substr('我是中⽂_测试_数据',-1) from dual--据
select substr('我是中⽂_测试_数据',-1,4) from dual--据
select substr('我是中⽂_测试_数据',-1,22) from dual--据
从上⾯的测试可以看出oracle下instr函数和mysql的substring_index函数很像，instr函数已经可以得到下标了，结合substr可以截取⼦串返回。
Mysql的
select substring_index('www.baidu', '.', 2);
Oralce可以这样做:
select substr('www.baidu', 1, instr('www.baidu', '.',1,2)-1) from dual
instr函数再-1是因为mysqlsubstring_index返回结果不包括.，上⾯返回的结果都是'www.baidu'。
Mysql的
select substring_index('www.baidu', '.', -2);
Oracle可以这样做:
select substr('www.baidu', instr('www.baidu', '.',-1,2) + 1, length('www.baidu'))from dual
select substr('www.baidu', instr('www.baidu', '.',-1,2) + 1) from dual
上⾯可以不使⽤length函数，因为oracle的substr默认截取到str最后⼀位。
中⽂测试如下:
Mysql下⾯：
select substring_index('我是中⽂_测试_数据','_',1) from dual;--我是中⽂
select substring_index('我是中⽂_测试_数据','_',2) from dual;--我是中⽂_测试
select substring_index('我是中⽂_测试_数据','_',-2) from dual;--测试_数据
select substring_index('我是中⽂_测试_数据','_',0) from dual;--空⽩字符串
select substring_index('我是中⽂_测试_数据','_',3) from dual;--我是中⽂_测试_数据
select substring_index('我是中⽂_测试_数据','.',3) from dual;--我是中⽂_测试_数据
Oracle下⾯：
select substr('我是中⽂_测试_数据', 1, instr('我是中⽂_测试_数据', '_',1,1)-1) from dual--我是中⽂
select substr('我是中⽂_测试_数据', 1, instr('我是中⽂_测试_数据', '_',1,2)-1) from dual--我是中⽂_测试
select substr('我是中⽂_测试_数据', instr('我是中⽂_测试_数据', '_',-1,2) + 1) from dual--测试_数据
select substr('我是中⽂_测试_数据', 1, instr('我是中⽂_测试_数据', '_',1,0)-1) from dual--ORA-01428：参数'0'超出范围
select substr('我是中⽂_测试_数据', 1, instr('我是中⽂_测试_数据', '_',1,3)-1) from dual--空⽩
select substr('我是中⽂_测试_数据', 1, instr('我是中⽂_测试_数据', '.',1,3)-1) from dual--空⽩
可以看到我写的这个有点问题，如果想在nth_appearance(>0)⼤于⽬标串在str中出现的次数时和mysql⼀样返回整个字符串，可以这样写：
select substr('我是中⽂_测试_数据',
1,
(select (case
when instr('我是中⽂_测试_数据', '_', 1, 3) <= 1 then
(select length('我是中⽂_测试_数据') from dual)
else
(instr('我是中⽂_测试_数据', '_', 1, 3) - 1)
end)
from dual))
from dual
这个在nth_appearance=0时⽤不了，进⼀步，考虑到nth_appearance=0时候返回NULL可以这样做，返回''这样的字符串使⽤substr 做不了。
select substr('我是中⽂_测试_数据',
1,
(select (case
substring和slicewhen 0 = 0 then
-1
else
case
when instr('我是中⽂_测试_数据', '_', 1, 3) <= 1 then
(select length('我是中⽂_测试_数据') from dual)
else
(instr('我是中⽂_测试_数据', '_', 1, 3) - 1)
end end)
from dual))
from dual
上⾯的sql是不是很丑陋，，我们可以定义⼀个存储过程封装⼀下。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。