Best way to cast from string to number - BigQuery

cristian2
Participant IV

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

0 2 8,363
2 REPLIES 2

Nope, this looks totally on the up and up to me. We have Looker expressions for use in table calculations (and there are to_string/to_number functions), but as far as a measure is concerned— Doing it in the SQL is the way to go.

cristian2
Participant IV

ok thanks!

Top Labels in this Space
Top Solution Authors