Question

oracle怎么截取指定字符后面的字符串，或者删除指定字符前面的字符串也行

例如：我要截取D:\AssetsManagement\resource\ehcache\test.txt 这段路径下的文件名test.txt。
求大神答疑

Answer 1

取最后一个\后面的字符
select substr('D:\AssetsManagement\resource\ehcache\test.txt',1+(select instr ('D:\AssetsManagement\resource\ehcache\test.txt','\',1,(select length('D:\AssetsManagement\resource\ehcache\test.txt') - length(replace('D:\AssetsManagement\resource\ehcache\test.txt','\','')) from dual)) from dual)
,length('D:\AssetsManagement\resource\ehcache\test.txt')-(select instr ('D:\AssetsManagement\resource\ehcache\test.txt','\',1,(select length('D:\AssetsManagement\resource\ehcache\test.txt') - length(replace('D:\AssetsManagement\resource\ehcache\test.txt','\','')) from dual)) from dual)
) from dual

如果前面D:\AssetsManagement\resource\ehcache\是固定的就直接replace了
select replace('D:\AssetsManagement\resource\ehcache\test.txt','D:\AssetsManagement\resource\ehcache\','') from dual

Answer 2

这个可以有。首先你得统计＂\＂出现的次数，我看到网上有代码，我是手机回答不好截图。意思就是将＂\＂替换成空。可以搜索：oracle 统计字符在字符串中出现的次数。有了斜杠出现的次数就可以用instrb函数取到最后出现的斜杠位置，再之后就用substr函数取最后斜杠至字符串结尾的字符即可。

Answer 3

用instr确定指定字符的位置，开始截取的位置就是这个位置+1，再用substr截取剩余字符。以你给的字符串为例：
SQL> select substr('D:\AssetsManagement\resource\ehcache\test.txt',instr('D:\AssetsManagement\resource\ehcache\test.txt','\',-1,1)+1) from dual;

SUBSTR('D:\ASSETSMANAGEMENT\RE
------------------------------
test.txt

Answer 4

用substr和INSTR配合使用