I'm trying to parse a dollar amount from a string.
Example strings:
I want to pull the patient liability, which is the dollar amount following text "LIAB," "PT LIAB," or "LIABLE." The dollar amount can be negative, and may or may not can have a decimal.
My solution:
REPLACE(REPLACE(REGEXP_SUBSTR(REMARKS,'LIAB+[LE]?+ (-?+\d+[.]?)+\d'),'LIAB ',''),'LIABLE ','')
This seems to be a bit clunky, and I assume there's a more simple solution. Any guidance on would be appreciated!
I'm using Toad for Oracle 12.8.
Give this a try:
SQL> with tbl(rownbr, remarks) as (
select 1, '*SOC 1369.00 - NCS 1239.46 = PT LIAB 129.54' from dual union
select 2, '*SOC 1369.00 - NCS 1239.46 = PT LIAB 140' from dual union
select 3, '*SOC = 1178.00' from dual union
select 4, '*SOC 1622.00 - NCS 209.74 = PT LIAB 1412.26 RECIPIENT AGE' from dual union
select 5, '*LINE #1 SOC 0.00 - NCS 22.77 = LIAB -22.77' from dual union
select 6, 'SOC MET AND CLEARED, SOC 2062-NCS 498.56=PT LIABLE 1563.44' from dual union
select 7, '*SOC 1622.00 - NCS 209.74 = PT LIAB 1412 RECIPIENT AGE 1234' from dual
)
select rownbr,
case
when remarks = regexp_replace(remarks, '.*((LIAB|LIABLE) ([-.0-9]+)).*$', '\3') then
'0' -- regexp_replace returns the orig string if the pattern is not found.
else
regexp_replace(remarks, '.*((LIAB|LIABLE) ([-.0-9]+)).*$', '\3')
end patient_liability
from tbl;
ROWNBR PATIENT_LIABILITY
---------- -------------------------
1 129.54
2 140
3 0
4 1412.26
5 -22.77
6 1563.44
7 1412
7 rows selected.
SQL>