如何用sql语句截取特定字符后的字符串

Question

C:\Windows\test.txtC:\WINDOWS\system32\abc\test1.docE:\Program Files\Foxmail\cryptdll\test12.dll
类似如此,路径长度不固定,需要取得最后以后'\'后面的文件名,想直接用sql查询语句实现,请高手帮忙

Answer 1

select 
substr('E:\Program Files\Foxmail\cryptdll\test12.dll',
instr('E:\Program Files\Foxmail\cryptdll\test12.dll','\',-1,1)+1)
from dual

Answer 2

来个正则的
select regexp_substr('E:\Program Files\Foxmail\cryptdll\test12.dll', '[^\]+$') from dual;

Answer 3

SELECT substr('C:\WINDOWS\system32\abc\test1.doc',INSTR('C:\WINDOWS\system32\abc\test1.doc', '\', -1, 1)+1)
FROM dual;
还是晚了一步

Answer 4

SELECT SUBSTR('E:\PROGRAM FILES\FOXMAIL\CRYPTDLL\TEST12.DLL',
              INSTR('E:\PROGRAM FILES\FOXMAIL\CRYPTDLL\TEST12.DLL',
                    '\',
                    1,
                    LENGTH('E:\PROGRAM FILES\FOXMAIL\CRYPTDLL\TEST12.DLL') -
                    LENGTH(REPLACE('E:\PROGRAM FILES\FOXMAIL\CRYPTDLL\TEST12.DLL',
                                   '\',
                                   ''))) + 1)
  FROM DUAL;

Answer 5

SQL> select substr('C:\WINDOWS\system32\abc\test1.doc',
instr('C:\WINDOWS\system32\abc\test1.doc','\',-1)+1) from dual;SUBSTR('C
---------
test1.doc

Answer 6

可以参考以下方案：
select instr('E:\Program Files\Foxmail\cryptdll\test12.dll','\',-1)
from dual