1

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:

enter image description here

G.Kol
  • 49
  • 5
  • How do you classify the word "negative"? Absence of a num/units combination so leave both NULL? Could you have a unit without a num? – Gary_W Feb 25 '20 at 20:15

1 Answers1

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks @gordon linoff.. Exactly what i was looking for. Thanks again. – G.Kol Feb 24 '20 at 19:10
  • To remove the dash before the leading number put the optional dash in front of the first group: `-?([0-9]*\.?[0-9]*)` – Gary_W Feb 25 '20 at 20:15