SQL Oracle multiply last digits of a string-CodePudding

I have a result of a query and need to get the last two numeric digits from a string column "contract"

The length of the string is not more than 3, and does not always have a digit

If the last two digits are numeric, then I want to multiply those numbers with 100. If the last 2 digits are not numeric, then I want to use a flat number of 20. How can I do this?

Appreciate your help

CodePudding user response：

Here's one option: check whether the last character is a digit (that's what regexp_like does); if so, extract digits anchored to the end of the string (that's regexp_substr) and do the math. Otherwise, return 20.

SQL> with test (contract) as
  2    (select 'B01' from dual union all
  3     select 'M03' from dual union all
  4     select 'R22' from dual union all
  5     select 'NV'  from dual union all
  6     select 'BTB' from dual
  7    )
  8  select contract,
  9         case when regexp_like(contract, '\d$ ') then regexp_substr(contract, '\d $') * 100
 10              else 20
 11         end as result
 12  from test;

CONTRACT       RESULT
---------- ----------
B01               100
M03               300
R22              2200
NV                 20
BTB                20

SQL>

CodePudding user response：

You may use ... default ... on conversion error feature of to_number function to convert numbers to numbers and everything else to nulls. Then handle default value with coalesce.

select
  contract,
  coalesce(
    /*Try to convert last two digits to number and then multiply by 100*/
    to_number(substr(contract, -2) default null on conversion error) * 100,
    20
  ) as num
from input_tab

CONTRACT	NUM
B01	100
M03	300
R22	2200
NV	20
BTB	20

db<>fiddle here