Best way to cast from string to number - BigQuery

Hi All!

I have a number in my bq-table that is stored as a string with this format “123.00-” or “123.00+”.

I need to remove the last character and cast this to a float64. My solution works fine but I don’t know if Im doing it the “looker” way. This is my solution:

measure: my_measure {

    type:  sum

    value_format_name: decimal_2

    sql:

        CAST(

          CASE

            WHEN substr(${TABLE}.string_field,-1)="+" THEN regexp_replace(${TABLE}.string_field, '[^0-9.]', '')

            WHEN substr(${TABLE}.string_field,-1)="-" THEN CONCAT("-",regexp_replace(${TABLE}.string_field, '[^0-9.]', ''))

          END

        AS FLOAT64)

          ;;

  }

Now, my question is if there’s another way of doing this with Looker functions? Any suggestions?

Cheers,

Cris