0

I want use regexp_substr to return the number after the characters from a string value

eg. String Value return 100AUS50 50 100AUS100 100 100AUS500 500
Jan-20 20 Four200 200

user15676
  • 123
  • 2
  • 10

2 Answers2

0

You can use the below to get the number 

 select regexp_substr(column,'\d+$') from TABLE;
psaraj12
  • 4,772
  • 2
  • 21
  • 30
0

Here's one way to do this using exclusively standard string functions (no regular expressions). The rtrim function simply removes the digits at the end of the input string. Then replace replaces that string (the input string minus the digits at the end) from the original string - therefore leaving the "digits at the end" in the output.

with
  sample_data (str) as (
    select '100AUS50'  from dual union all
    select '100AUS100' from dual union all
    select '100AUS500' from dual union all
    select 'Jan-20'    from dual union all
    select 'Four200'   from dual union all
    select null        from dual union all
    select '200abc'    from dual union all
    select '3'         from dual
  )
select str, replace(str, rtrim(str, '0123456789')) as last_digits
from   sample_data
;

STR       LAST_DIGITS
--------- -----------
100AUS50  50       
100AUS100 100      
100AUS500 500      
Jan-20    20       
Four200   200      

200abc             
3         3

In the past I would just have said "standard string functions are generally faster than regular expressions". As luck would have it, though, I just answered the same question on OTN a few days ago, and the poster asked about the performance comparison. On a sample of 1.5 million input strings, the solution I just showed here was five times faster (0.86 seconds vs. 4.4 seconds for the regexp solution). https://community.oracle.com/message/15598613#15598613