How to extract words from space in oracle?

Question

I have a sentence in a column filename such as:

filename
Exact filename from California Dec 2015
Exact filename from Austin Jan 2015

I want to only extract date part from it like my expected output is:

filename
Dec 2015
Jan 2015

However I searched in Stackoverflow but I found two result but they didnot worked for me.

select regexp_substr(regexp_replace(filename
                                   ,'[[:space:]]-[[:space:]]'
                                   ,chr(11))
                    ,'([^'||chr(11)||']*)('||chr(11)||'|$)'
                    ,1 -- Start here
                    ,4 -- return 1st, 2nd, 3rd, etc. match
                    ,null
                    ,1 -- return 1st sub exp
                    )

Another method I tried is:

regexp_like(filename,'(^[:space:]|[:space:]$)');

However these both solution didnot worked for me.I searched more on SO but didnt got it.

Answer 1

If you just need the last two "words" in your column values, you can use this:

select regexp_substr(filename, '[^[:space:]]+[[:space:]][^[:space:]]+$') from table;

This expression matches: (anything except space)(space)(anything except space)(end)

Note that this only allows for one whitespace character between the last two words though.