oracle 使用 regexp_instr 获取字符串中的最后一个数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9755681/
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
Use regexp_instr to get the last number in a string
提问by Anna Lam
If I used the following expression, the result should be 1.
如果我使用以下表达式，结果应该是 1。
regexp_instr('500 Oracle Parkway, Redwood Shores, CA','[[:digit:]]')
Is there a way to make this look for the last number in the string? If I were to look for the last number in the above example, it should return 3.
有没有办法让这个查找字符串中的最后一个数字？如果我要查找上面示例中的最后一个数字，它应该返回 3。
回答by Justin Cave
If you were using 11g, you could use regexp_countto determine the number of times that a pattern exists in the string and feed that into the regexp_instr
如果您使用的是 11g，则可以使用regexp_count来确定模式在字符串中存在的次数并将其输入regexp_instr
regexp_instr( str,
'[[:digit:]]',
1,
regexp_count( str, '[[:digit:]]')
)
Since you're on 10g, however, the simplest option is probably to reverse the string and subtract the position that is found from the length of the string
但是，由于您使用的是 10g，因此最简单的选择可能是反转字符串并从字符串的长度中减去找到的位置
length(str) - regexp_instr(reverse(str),'[[:digit:]]') + 1
Both approaches should work in 11g
这两种方法都应该适用于 11g
SQL> ed
Wrote file afiedt.buf
1 with x as (
2 select '500 Oracle Parkway, Redwood Shores, CA' str
3 from dual
4 )
5 select length(str) - regexp_instr(reverse(str),'[[:digit:]]') + 1,
6 regexp_instr( str,
7 '[[:digit:]]',
8 1,
9 regexp_count( str, '[[:digit:]]')
10 )
11* from x
SQL> /
LENGTH(STR)-REGEXP_INSTR(REVERSE(STR),'[[:DIGIT:]]')+1
------------------------------------------------------
REGEXP_INSTR(STR,'[[:DIGIT:]]',1,REGEXP_COUNT(STR,'[[:DIGIT:]]'))
-----------------------------------------------------------------
3
3
回答by ShoeLace
Another solution with less effort is
另一个省力的解决方案是
SELECT regexp_instr('500 Oracle Parkway, Redwood Shores, CA','[^[:digit:]]*$')-1
FROM dual;
this can be read as.. find the non-digits at the end of the string. and subtract 1. which will give the position of the last digit in the string..
这可以读作.. 找到字符串末尾的非数字。并减去 1. 这将给出字符串中最后一位数字的位置..
REGEXP_INSTR('500ORACLEPARKWAY,REDWOODSHORES,CA','[^[:DIGIT:]]*$')-1
--------------------------------------------------------------------
3
which i think is what you want.
我认为这就是你想要的。
(tested on 11g)
（在 11g 上测试）
