Search code examples
sqlstringoracle-databaseoracle12c

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?

Solution

  • 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