Using regex in sql to define new variable

I need to derive the “new” column from the “old” column using sql and regex if possible. I’m using Oracle SQL Developer. If I were using regex in R or Python, I would use this recipe to get the “new” column:

[1,2,3,4,5,6,7,8,9]{1,5}|b0b

old              new
P003             3 
4                4 
P00005           5
P0005            5
12               12
P00000016        16
0                0

Thanks.

Use this:

REGEXP_SUBSTR(old, '[1-9]{1,9}0{0,10}|[1-9]{1,5}|b0b') as new

Answer

Here’s one option:

SQL> with test (old) as
  2    (select 'P003'      from dual union all
  3     select '4'         from dual union all
  4     select 'P00005'    from dual union all
  5     select 'P0005'     from dual union all
  6     select '12'        from dual union all
  7     select 'P00000016' from dual union all
  8     select '0'         from dual
  9    )
 10  select old, to_number(regexp_substr(old, 'd+')) new
 11  from test;

OLD              NEW
--------- ----------
P003               3
4                  4
P00005             5
P0005              5
12                12
P00000016         16
0                  0

7 rows selected.

SQL>