Oracle regexp to split numbers and units

Question

I'm trying to extract numbers and units from a input string. I was able to extract the number, but unable extract the units.

WITH smpl AS 
(

    SELECT '8.23 ug/dL' str FROM DUAL UNION
    SELECT 'negative' FROM DUAL UNION
    SELECT '29.2 ' FROM DUAL UNION
    SELECT '0.2 E.U./dL' FROM DUAL UNION
    SELECT '-150 ng/mL' FROM DUAL 
)
SELECT 
    str, 
    regexp_substr(str, '\.*[[:digit:]]+\.*[[:digit:]]*') num
FROM smpl;

desired output:

Answer 1

I think this does what you want:

SELECT str, 
       nullif(regexp_replace(str, '(-?[0-9]*\.?[0-9]*) ([^[:digit:]]*)$', '\1'), str) as num,
       nullif(regexp_replace(str, '(-?[0-9]*\.?[0-9]*) ([^[:digit:]]*)$', '\2'), str) as unit
FROM smpl;

The nullif() handles the situation where nothing matches -- returning NULL rather than the full string.

Here is a db<>fiddle.