oracle 12c - 在最后一次出现字符后选择字符串

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?

所以我想选择Sentence,因为它是最后一段时间后的字符串。我怎样才能做到这一点?

4 个解决方案

#1

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:

没有什么比测试更能看到当字符串结束时这不起作用。关于 - 0 = 0的事情。这是一个改进:

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.

您的示例在我在本地Oracle和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

#2

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

使用锚$来限制匹配到字符串的结尾

#3

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.

这里的区别在于我们使用-1来向后计数以找到最后一个。什么时候做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;

#4

-1

how many dots in a string?

字符串中有多少个点?

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

get substring after last dot:

在最后一个点后得到子串:

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

#1