TSQL - Comma formatted number from varchar to float

select distinct

       FIELD_NAME_HERE

       , cast(

              case

                     -- If the entire field is a dash, then turn it to null

                     -- If it is null, then keep it as null

                     when

                           FIELD_NAME_HERE='-'

                           or FIELD_NAME_HERE is null

                     then

                           null

                     -- If the cell has an actual value

                     else

                           -- Comma to nothing

                           replace(

                                  -- Ending parenthesis to nothing

                                  replace(

                                         -- Starting parenthesis with a minus sign

                                         replace(FIELD_NAME_HERE,'(','-')

                                         , ')'

                                         , ''

                                  )

                                  ,','

                                  , ''

                           )

              end

              as float

       ) as FIELD_NAME_HERE_NEW

from

       DATABASE_NAME_HERE.SCHEMA_NAME_HERE.TABLE_NAME_HERE

;