Use regexp_instr to get the last number in a string

Question

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

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

Is there a way to do this to find the last number in a string? If I were to find the last number in the above example, it should return 3.

Answer 1

If you used 11g, you can use regexp_count to determine the number of times that the pattern exists in the string and feed it to regexp_instr

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

However, since you are using 10g, the easiest option is probably to change the line and subtract the position found along the length of the line

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

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 

Answer 2

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 non-digits at the end of the line. and subtract 1., which gives the position of the last digit in the line.

 REGEXP_INSTR('500ORACLEPARKWAY,REDWOODSHORES,CA','[^[:DIGIT:]]*$')-1 -------------------------------------------------------------------- 3 

which I think you like.

(checked on 11g)