转：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,instr(name,'-',1,1)+1,instr(name,'-',1,2)-1-instr(name,'-',1,1)) as name2,
 substr(name,instr(name,'-',-1)+1) as name_1
from test;

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

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