SQL & PL/SQL

I have a value that unfortunately is in a free form text varchar(50) field and I am updating a table that is a NUMBER field.  The data could be anything.  From my research it looks like its values like 18000, or $20,000, or $23,000.00, or $28,000.00, or $35,000.0000.

I have tried the To_Number command and it failed.

I did a replace function and stripped out dollar signs, and commas and even empty spaces, and as a last resort even stripped out periods just to try to put bad data in, thinking maybe the free text field had two periods in it.

I have an IS_NUMBER function and when i run the field thru there, it says they are all numbers but when i update it fails saying it is an invalid number.

I have been a msft sql dba for years but Oracle is brand new to me.

BEGIN

merge into wh_cust_act_brand t

    using (

    select cust_no, activ_no, ss.SETTING_VALUE

      FROM GMSTG.STG_service_settings ss

      WHERE ss.setting_id = 'lifeline_qualifying_income'

        and ss.setting_value is not null

        and not exists (select 1

                             from wh_cust_act_brand c

                               where c.cust_no = ss.cust_no

                               and c.activ_no = ss.activ_no

                               and c.lifeline_qualifying_income = ss.setting_value

                                )) q

  ON (t.cust_no = q.cust_no and t.activ_no = q.activ_no)

  when matched then

    update set

           t.update_date = sysdate,

i tried these:

-- t.lifeline_qualifying_income = q.setting_value

-- t.lifeline_qualifying_income = replace(replace(replace(replace(q.setting_value, '$', ''), ',', ''), '-', ''), ' ', '');

-- t.lifeline_qualifying_income = TO_NUMBER(replace(replace(replace(replace(q.setting_value, '$', ''), ',', ''), '-', ''), ' ', ''), '$9,999,999.99999999');

-- t.lifeline_qualifying_income = cast(regexp_replace(q.setting_value, '[^0-9.]+', '') as number);

-- t.lifeline_qualifying_income = regexp_replace(q.setting_value, '[^0-9]+', '');            

      t.lifeline_qualifying_income =

         (CASE

                WHEN substr(q.setting_value, 1, 1) = '$'  then to_number(substr(replace(q.setting_value, ',', ''), 2, length(q.setting_value)-1), '9999999.99999999')

                WHEN substr(q.setting_value, 1, 1) <> '$' then to_number(replace(q.setting_value, ',', ''), '999999.9999')

           -- ELSE '0'

          END);

EXCEPTION

   WHEN OTHERS THEN

raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);

END;