ORACLE - expression that picks a number from string-CodePudding

I have this table, I need to pick the number before the letters "kV" or "KV". Also in the last example el number is without space at the beginning.

Here the example:

select device_name from table;

--device_name can be:

15518 - 132 Garden Plain 138 kV l/o 0405 result 138
ALLENIM 345 KV  ALL-RPM_I.  result=345
179 BLOO345 KV  TR84CT-P_I.  result= 345.  --on this case the number is withoutspace

What kind of regular expression I can use to pick these number?

Regards

CodePudding user response：

Here's one option: find digits in front of upper KV, and then just remove KV:

SQL> with test (col) as
  2    (select '15518 - 132 Garden Plain 138 kV l/o 0405' from dual union all-- result 138
  3     select 'ALLENIM 345 KV  ALL-RPM_I.'               from dual union all-- result=345
  4     select '179 BLOO345 KV  TR84CT-P_I.'              from dual          -- result=345
  5    )
  6  select col,
  7    replace(regexp_substr(upper(col), '\d (\s?)KV'), 'KV', '') result
  8  from test;

COL                                      RESULT
---------------------------------------- --------------------
15518 - 132 Garden Plain 138 kV l/o 0405 138
ALLENIM 345 KV  ALL-RPM_I.               345
179 BLOO345 KV  TR84CT-P_I.              345

SQL>