Oracle regexp - reformat a string to date-like information

Question

A column holds the name of a special kind of files, which are named like this: "kontrolrapport-2015.12.23-10.19.05.pdf". I want to extract the date, that is "2015-12-23 10:19". I thought it would be peace of cake, but it's takes a little bit more, than I'm able to figure out.
This is how far I get
select 'kontrolrapport-2015.12.23-10.19.05.pdf' as file_name, regexp_replace('kontrolrapport-2015.12.23-10.19.05.pdf','(\w*)-', '') as date_like from dual;

Answer 1

Based on your example regex, I'm assuming there is a variable prefix in your file name followed by a hyphen followed by date followed by time and then a file extension.

Here is the regex I used to extract each piece of information -

with f as (SELECT 'kontrolrapport-2015.12.23-10.19.05.pdf' AS file_name,
       '([^-]+)-([0-9\.]+)-([0-9\.]+)\.([a-z]+)' AS regex
  FROM DUAL)
select  f.file_name, 
        regexp_substr (f.file_name, f.regex, 1, 1, 'i', 1) as prefix_part,
        regexp_substr (f.file_name, f.regex, 1, 1, 'i', 2) as date_part,
        regexp_substr (f.file_name, f.regex, 1, 1, 'i', 3) as time_part,
        regexp_substr (f.file_name, f.regex, 1, 1, 'i', 4) as file_extension
 from f;

Start with this and maybe improve on it further. I'd replace [a-z] and [0-9] with POSIX classes.