转：Oracle中截取指定字符之间的字符串，获取特定字符串在字符串中出现的次数

一、Oracle中通过substr和instr实现截取指定字符之间的字符串

https://blog.csdn.net/chinasxdtzhaoxinguo/article/details/84663351

test表name字段 是字符串，型如 CA-CA1344-A-11OCT141250-D
需求一：按照-分隔，去第二段

select substr(name,instr(name,'-',1,1)+1,instr(name,'-',1,2)-1-instr(name,'-',1,1)) from test;

需求一：按照-分隔，去最后一段

select substr(name,instr(name,'-',-1)+1) from test;

二、获取特定字符串在字符串中出现的次数

https://blog.csdn.net/changerzhuo_319/article/details/86916413

select length(',abc,,,d,,ef,') - length(replace(',abc,,,d,,ef,', ',','')) from dual;
select length(name) - length(replace(name, '-','')) +1 as total from test;

三、case用法

https://www.cnblogs.com/wangrui1587165/p/9999961.html
https://blog.csdn.net/sdut406/article/details/81090510
语法 case when then else end

select
 name,
 CASE 
  when length(name) - length(replace(name, '-','')) +1 = 3
   THEN substr(name,instr(name,'-',1,1)+1,instr(name,'-',1,2)-1-instr(name,'-',1,1))
  ELSE
   NULL
 END AS source_schema,
 CASE length(name) - length(replace(name, '-','')) +1
  when 3
   THEN substr(name,instr(name,'-',1,1)+1,instr(name,'-',1,2)-1-instr(name,'-',1,1))
  ELSE
   NULL
 END AS source_schema_tmp,
 -- 分段总数
 length(name) - length(replace(name, '-','')) +1 as total,
 -- 取第一段
 substr(name,1,instr(name,'-',1,1)-1) as name1,
 -- 取第二段
 substr(name,instr(name,'-',1,1)+1,instr(name,'-',1,2)-1-instr(name,'-',1,1)) as name2,
 -- 取第三段
 substr(name,instr(name,'-',2,2)+1,instr(name,'-',2,3)-1-instr(name,'-',2,2)) as name3,
 -- 取最后一段
 substr(name,instr(name,'-',-1)+1) as name_1,
 -- 取倒数第二段
 substr(name, instr(name,'-',-2,2)+1, instr(name,'-',-1)-1-instr(name,'-',-2,2)) as name_2
from test;

四、MySQL与Oracle 差异比较之函数

https://www.cnblogs.com/HondaHsu/p/3641190.html