CODEWITHSUNDEEP
google-bigquery
nrad
nrad

Reputation: 93

How to convert percent 56.67% (string data type) as float64

I need to convert data which is shown as 56.67% (string data type). I need to convert it as FLOAT64. I am unable to do it. Can anyone help please.

Upvotes: 0

Views: 1164

Answers (3)

Alexandre Moraes
Alexandre Moraes

Reputation: 4042

In order to further contribute to the community, in addition to Felipe Hoffa and Mikhail Berlyant answers. I will propose a slight variation for their answers.

BigQuery supports User Defined Functions (UDF) which allows you to create a function using SQL or JavaScript. It is very useful when you need to pass the columns as input, perform some actions and return a result as a value. The below approach uses UDF and REGEXP_EXTRACT() builtin method,

CREATE TEMP FUNCTION format_float(x STRING) AS (
CAST(regexp_extract(x, r'.*[^\%]') as float64)/100
);

with data as (
select "55.58%" as percentage UNION ALL
select "60.01%" as percentage
)

select format_float(percentage) as new_percentage from data

and the output,

Row new_percentage
1   0.5558
2   0.6001

Notice that, the function format_float is used within the select. Also, the regex expression uses .* plus negative look ahead, which means that it will match any character apart from the % symbol. Then the value is casted to float64 and divided by 100 in order to be formatted accordingly.

Note: you can wrap any of the other provided answers in a UDF, following the same above syntax,if you desire.

Upvotes: 0

Felipe Hoffa
Felipe Hoffa

Reputation: 59175

Some alternatives:

SELECT SAFE_CAST(x AS FLOAT64)
  , CAST(REGEXP_EXTRACT(x, r'[0-9\.\-]*') AS FLOAT64)
  , CAST(REGEXP_EXTRACT(x, r'[0-9\.\-]*') AS FLOAT64)/100
  , CAST(REGEXP_EXTRACT(x, r'[0-9\.\-]*') AS NUMERIC)/100
FROM (
  SELECT "56.67%" x
  UNION ALL
  SELECT "1.001%" 
)

Results:

null
56.67
0.5667
0.5667

null
1.001
0.010009999999999998
0.01001

As seen above, FLOAT64 can exhibit some "strange" behaviors, and NUMERIC can help in those cases.

Upvotes: 0

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

Use below

SAFE_CAST(REPLACE(data, '%', '') AS Float64)/100

Upvotes: 1

Related Questions