using oracle sql substr to get last digits

Question

I have a result of a query and am supposed to get the final digits of one column say 'term'

The value of column term can be like:
'term'     'number' (output)
---------------------------
xyz012         12
xyz112         112
xyz1           1
xyz02          2
xyz002         2
xyz88          88

Note: Not limited to above scenario's but requirement being last 3 or less characters can be digit

Function I used: to_number(substr(term.name,-3)) (Initially I assumed the requirement as last 3 characters are always digit, But I was wrong)

I am using to_number because if last 3 digits are '012' then number should be '12' But as one can see in some specific cases like 'xyz88', 'xyz1') would give a

ORA-01722: invalid number

How can I achieve this using substr or regexp_substr ?

Did not explore regexp_substr much.

Answer 1

Using REGEXP_SUBSTR,

select column_name, to_number(regexp_substr(column_name,'\d+$'))
 from table_name;

• \d matches digits. Along with +, it becomes a group with one or more digits.
• $ matches end of line.
• Putting it together, this regex extracts a group of digits at the end of a string.

More details here.

Demo here.