Oracle 12c - select a row after the last occurrence of a character

Question

I have a line below:

ThisSentence.ShouldBe.SplitAfterLastPeriod.Sentence 

So, I want to select Sentence , as this is the row after the last period. How can i do this?

Answer 1

Perhaps you can do this with complex regular expressions. I like the following method:

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

Nothing like testing to see that this does not work when the line is at the end. Something about - 0 = 0. Here is the improvement:

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

EDIT:

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

I run 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 

Answer 2

Just for completeness, here is a regex solution (not very complicated IMHO :-)):

 select regexp_substr( 'ThisSentence.ShouldBe.SplitAfterLastPeriod.Sentence', '[^.]+$') from dual 

Regular expression

• uses a negative character class to match anything except the dot [^.]
• adds a quantifier + to match one or more of these
• uses the $ binding to limit matches to the end of the line

Answer 3

And one more way.

Not sure in terms of performance that would be best ...

The difference here is that we use -1 to count backwards to find the last. when doing instr.

  With CTE as (Select 'ThisSentence.ShouldBe.SplitAfterLastPeriod.Sentence' str, length('ThisSentence.ShouldBe.SplitAfterLastPeriod.Sentence') len from dual) Select substr(str,instr(str,'.',-1)+1,len-instr(str,'.',-1)+1) from cte; 

Answer 4

how many points in a row?

 select length(str) - length(replace(str, '.', '') number_of_dots from ... 

get the substring after the last point:

 select substr(str, instr(str, '.', 1, number_of_dots)+1) from ...