Finding the first four numbers in a string using Oracle SQL

Question

I have to find the first sequence of four numbers inside a table using Oracle SQL.

Inside the input_file_name column we can find values as:

RVSP_040517.M
SERIES_040517_CP.TXT
SAUDE_O10N0505.M
SERIES_040517.txt
RVSP_080517.M
SERIES_080517_CP.TXT

As we can see, there is groups of numbers before, but the first group of four numbers from the left is what I want. I want to create a new column with this day-mouth (4) numbers.

How can I do it?

The result I am expecting from this table is:

I was trying to use inStr but it wasn't working

score 2 · Accepted Answer · 2017-06-26 13:43:09Z

select ..., regexp_substr(input_file_name, '\d{4}') as day_month, ...
from   ...

Demo (with a few more strings to show what happens when there aren't any substrings of four consecutive digits, or more than one occurrence of such substrings):

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

Collectives™ on Stack Overflow

Finding the first four numbers in a string using Oracle SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related