我必须使用Oracle SQL在表中找到四个数字的第一个序列。
在 input_file_name 列中,我们可以找到值:
RVSP_040517.M
SERIES_040517_CP.TXT
SAUDE_O10N0505.M
SERIES_040517.txt
RVSP_080517.M
SERIES_080517_CP.TXT
正如我们所看到的,以前有一组数字,但是我想要的四个数字是四个数字的第一组。我想用这一天的嘴(4(数字创建一个新列。
我该怎么做?
我期望从此表的结果是:
0405
0405
0505
0405
0805
0805
我试图使用 inst ,但它不起作用
select ..., regexp_substr(input_file_name, 'd{4}') as day_month, ...
from ...
演示(还有几个字符串可以显示没有四个连续数字的子字符串或多个出现此类子字符串的情况(:
with
test_data ( input_file_name ) as (
select 'RVSP_040517.M' from dual union all
select 'SERIES_040517_CP.TXT' from dual union all
select 'SAUDE_O10N0505.M' from dual union all
select 'SERIES_040517.txt' from dual union all
select 'RVSP_080517.M' from dual union all
select 'SERIES_080517_CP.TXT' from dual union all
select 'mathguy wins' from dual union all
select 'GOOD_031.pdf' from dual union all
select 'FOUR_DIGITS_123_4' from dual union all
select 'A22409_11230.cpp' from dual
)
-- End of simulated data (for testing purposes only, not part of the solution).
-- SQL query begins BELOW THIS LINE.
select input_file_name, regexp_substr(input_file_name, 'd{4}') as day_month
from test_data
;
INPUT_FILE_NAME DAY_MONTH
-------------------- ------------
RVSP_040517.M 0405
SERIES_040517_CP.TXT 0405
SAUDE_O10N0505.M 0505
SERIES_040517.txt 0405
RVSP_080517.M 0805
SERIES_080517_CP.TXT 0805
mathguy wins
GOOD_031.pdf
FOUR_DIGITS_123_4
A22409_11230.cpp 2240