oracle 12c – select string after last occurrence of a character

I have below string:

ThisSentence.ShouldBe.SplitAfterLastPeriod.Sentence

So I want to select Sentence since it is the string after the last period. How can I do this?

Answer

You can probably do this with complicated regular expressions. I like the following method:

select substr(str, - instr(reverse(str), '.') + 1)

Nothing like testing to see that this doesn’t work when the string is at the end. Something about – 0 = 0. Here is an improvement:

select (case when str like '%.' then ''
             else substr(str, - instr(reverse(str), ';') + 1)
        end)

EDIT:

Your example works, both when I run it on my local Oracle and in SQL Fiddle.

I am running this code:

select (case when str like '%.' then ''
             else substr(str, - instr(reverse(str), '.') + 1)
        end)
from (select 'ThisSentence.ShouldBe.SplitAfterLastPeriod.Sentence' as str from dual) t