SQL oracle 12c - 在最后一次出现字符后选择字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24084644/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
oracle 12c - select string after last occurrence of a character
提问by user3224907
I have below string:
我有以下字符串：
ThisSentence.ShouldBe.SplitAfterLastPeriod.Sentence
So I want to select Sentencesince it is the string after the last period. How can I do this?
所以我想选择，Sentence因为它是最后一段之后的字符串。我怎样才能做到这一点？
回答by Frank Schmitt
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
- 使用否定字符类来匹配除点以外的任何内容
[^.] - 添加一个量词
+来匹配这些中的一个或多个 - 使用锚点
$将匹配限制在字符串的末尾
回答by Gordon Linoff
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
回答by xQbert
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 向后计数以找到最后一个 。在做指令时。
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;
回答by Harel.Sh
select
substr(
'ThisSentence.ShouldBe.SplitAfterLastPeriod.Sentence',
INSTR(
'ThisSentence.ShouldBe.SplitAfterLastPeriod.Sentence',
'.',
-1
)+1
)
from dual;
回答by vav
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 ...
