Question

Best way to cast from string to number - BigQuery

  • 20 August 2019
  • 2 replies
  • 5503 views

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


2 replies

Userlevel 7
Badge +1

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.

ok thanks!

Reply