using oracle sql substr to get last digits
oracle, regex, sql, substr
Solution
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.
Problem
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.