user3224907 user3224907 - 1 year ago 190
SQL Question

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 Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download