使用regexp_instr获取字符串中的最后一个数字 [英] Use regexp_instr to get the last number in a string

问题描述

如果我使用以下表达式，则结果应为1.

If I used the following expression, the result should be 1.

regexp_instr('500 Oracle Parkway, Redwood Shores, CA','[[:digit:]]')

有没有办法让它寻找字符串中的最后一个数字?如果要在上面的示例中查找最后一个数字，则应返回3.

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.

推荐答案

如果您使用的是11g，则可以使用regexp_count确定字符串中存在模式的次数，并将其输入regexp_instr

If you were using 11g, you could use regexp_count to determine the number of times that a pattern exists in the string and feed that into the regexp_instr

regexp_instr( str,
              '[[:digit:]]',
              1,
              regexp_count( str, '[[:digit:]]')
            )

但是，由于您的重量是10克，最简单的选择可能是反转字符串并从字符串的长度中减去找到的位置

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

length(str) - regexp_instr(reverse(str),'[[:digit:]]') + 1

这两种方法都应该在11g内起作用

Both approaches should work in 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

这篇关于使用regexp_instr获取字符串中的最后一个数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！