如何在oracle中获取特殊字符之间的数字

Question

您能帮助在oracle 中获得以下输出吗

File_20170902_Name.txt
File200_Name_20170902_1.txt
File400_20170902_Name_1.txt
File1_name_20170902.txt

我期待像一样的输出

20170902
20170902
20170902
20170902

提前感谢！！

Answer 1

假设文件名中的所有8位数字都是日期，并且前面是_，后面是_或.txt，则可以使用

select REGEXP_SUBSTR(files,'_(d{8})(_|.txt)',1,1,NULL,1) as dt FROM t;

演示

但是一些时间数字字符会像6(YYYMDD(字符一样变化

这将匹配_或_与.txt之间的6到8个字符的序列

REGEXP_SUBSTR(files,'_((d{6,8}))(_|)',1,1,NULL,1)

Demo2

但是，这不是一个全面的正则表达式来匹配日期。即，如果CCD_ 7或CCD_。如果您只想检测日期，那么您可能必须将它们与此处指定的方法相结合：Oracle 中的有效日期检查

Answer 2

试试这个演示：http://sqlfiddle.com/#！4/c361c/4

select
regexp_replace(str,'.*(d{8}).*','1') as x
from table1
|        X |
|----------|
| 20170902 |
| 20170902 |
| 20170902 |
| 20170902 |

Answer 3

试试这个

create table tst (x varchar2(30));
insert into tst values ('File_20170902_Name.txt');
insert into tst values ('File200_Name_20170902_1.txt');
insert into tst values ('File400_20170902_Name_1.txt');
insert into tst values ('File1_name_20170902.txt');
Select x,       
case 
when substr(x, REGEXP_INSTR(x, '[^a-z|A-Z|0-9]') + 1, 1) between chr(48) and chr(57) Then 
substr(x, REGEXP_INSTR(x, '[^a-z|A-Z|0-9]') + 1, REGEXP_INSTR(x, '[^a-z|A-Z|0-9]', 1, 2) - REGEXP_INSTR(x, '[^a-z|A-Z|0-9]') - 1) 
else substr(x, REGEXP_INSTR(x, '[^a-z|A-Z|0-9]', 1, 2) + 1, REGEXP_INSTR(x, '[^a-z|A-Z|0-9]', 1, 3) - REGEXP_INSTR(x, '[^a-z|A-Z|0-9]', 1, 2) - 1) 
end as t      
From tst  

注意：如果数字在两个以上的特殊字符后开始，如File_name_test_20170902.txt ，则此查询不支持

Answer 4

以下两者的工作原理大致相同-使用正则表达式进行基本选择，然后使用对REGEXP_SUBSTR的第二次调用或使用"normal"字符串函数来消除分隔符。与使用TRANSLATE或REPLACE相比，我更喜欢对REGEXP_SUBSTR的嵌套调用，但您可以选择：

对REGEXP_SUBSTR:的嵌套调用

WITH cteFiles AS (SELECT 'File_20170902_Name.txt' AS FILENAME FROM DUAL UNION ALL
SELECT 'File200_Name_20170902_1.txt' AS FILENAME FROM DUAL UNION ALL
SELECT 'File400_20170902_Name_1.txt' AS FILENAME FROM DUAL UNION ALL
SELECT 'File1_name_20170902.txt' AS FILENAME FROM DUAL)
SELECT FILENAME, REGEXP_SUBSTR(REGEXP_SUBSTR(FILENAME, '_[0-9]+[_.]'), '[^_.]+') AS FINAL_NUMS
FROM cteFiles;

使用REPLACE:

WITH cteFiles AS (SELECT 'File_20170902_Name.txt' AS FILENAME FROM DUAL UNION ALL
SELECT 'File200_Name_20170902_1.txt' AS FILENAME FROM DUAL UNION ALL
SELECT 'File400_20170902_Name_1.txt' AS FILENAME FROM DUAL UNION ALL
SELECT 'File1_name_20170902.txt' AS FILENAME FROM DUAL)
SELECT FILENAME, REPLACE(REPLACE(REGEXP_SUBSTR(FILENAME, '_[0-9]+[_.]'), '_', NULL), '.', NULL) AS FINAL_NUMS
FROM cteFiles;

使用TRANSLATE:

WITH cteFiles AS (SELECT 'File_20170902_Name.txt' AS FILENAME FROM DUAL UNION ALL
SELECT 'File200_Name_20170902_1.txt' AS FILENAME FROM DUAL UNION ALL
SELECT 'File400_20170902_Name_1.txt' AS FILENAME FROM DUAL UNION ALL
SELECT 'File1_name_20170902.txt' AS FILENAME FROM DUAL)
SELECT FILENAME, TRANSLATE(REGEXP_SUBSTR(FILENAME, '_[0-9]+[_.]'), '0123456789_.', '0123456789') AS FINAL_NUMS
FROM cteFiles;

SQLFiddle此处

祝你好运。