Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 997
user3224907 Avatar asked Jun 06 '14 14:06

user3224907


People also ask

How do you extract a particular word from a string in Oracle?

SELECT REGEXP_SUBSTR ( 'select * from TEMP_TABLE where trunc(xyz_xyz) = ''xyz'' and trunc(abc_abc) = ''abc'')' , ' trunc[^'']''([^'']+)''' , 1 , n , 'i' , 1 ) FROM dual ; returns the n-th such substring.

How do you check the occurrence of a character in a string in Oracle?

The Oracle REGEXP_COUNT function is used to count the number of times that a pattern occurs in a string. It returns an integer indicating the number of occurrences of a pattern. If no match is found, then the function returns 0.

How do I remove a character from a string in Oracle?

Description. The Oracle LTRIM() function is used to remove all specified characters from the left end side of a string. Optionally you can specify an initial character or characters to trim to, or it will default to a blank. The string to trim the characters from the left-hand side.


3 Answers

Just for completeness' sake, here's a solution using regular expressions (not very complicated IMHO :-) ):

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

The regex

  • uses a negated character class to match anything except for a dot [^.]
  • adds a quantifier + to match one or more of these
  • uses an anchor $ to restrict matches to the end of the string
like image 177
Frank Schmitt Avatar answered Oct 21 '22 01:10

Frank Schmitt


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
like image 24
Gordon Linoff Avatar answered Oct 21 '22 02:10

Gordon Linoff


And yet another way.

Not sure from a performance standpoint which would be best...

The difference here is that we use -1 to count backwards to find the last . when doing the 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;
like image 23
xQbert Avatar answered Oct 21 '22 01:10

xQbert