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.

Solution 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.

Solution 2

Oracle has the function regexp_instr() which does what you want:

select term, cast(substr(term, 1-regexp_instr(reverse(term),'[^0-9]')) as int) as number

Comments

[user1933888]

Thx. This works for me. Though I have one question: Any advantage of using 'cast as int' instead of to_number, both do the same in this scenario. So basically you reverse string find position of first non digit number, 1- <position> to get the no of last digits for substr, cool

[user1933888]

Thx. It works, but just curious what exactly does the regEx '\d+$' Do. I accepted answer of the person who gave solution first.

[Gordon Linoff]

@user1933888 . . . I don't think there is a difference. I have a tendency to ANSI standard constructs (that work across databases), but the Oracle vernacular would be to_number().

[user1933888]

ok, thx for the explanation you guys are super cool. Though in moral dilemma weather to accept your answer or that of 'Ramblin' Man' as his solution looks more elegant.

[user1933888]

Thx, you rock!. Your's is the most elegant solution.

[Nathan Tuggy]

Does not appear to handle multiple digits.

[tom redfern]

@Tordek how can this not provide an answer to the question when the answer quite clearly does provide an answer?

[tom redfern]

Code-only answers are auto-flagged as low quality and as such are discouraged on stackoverflow. In the future please embellish your answer with details and explain why it is a solution to the question. This helps other users.