CODEWITHSUNDEEP
sqlgoogle-bigquery
Stewart_R
Stewart_R

Reputation: 14515

BigQuery Standard SQL: Setting a value to positive or negative infinity

I note from the docs for BigQuery Standard SQL Mathematical functions that we can test if a number "is infinity" using the IS_INF() function something like this:

WITH demo_tbl AS (
  SELECT 1 AS val UNION ALL
  SELECT 100 AS val
)

SELECT 
  val,
  IS_INF(val) as is_infinity
FROM demo_tbl

which outputs:

+---+-----+-------------+
|   | val | is_infinity |
+---+-----+-------------+
| 0 |   1 | False       |
| 1 | 100 | False       |
+---+-----+-------------+

but is it possible to explicitly set a value to be positive or negative infinity using some constant value or key word?

Perhaps something like this:

WITH demo_tbl AS (
  SELECT 1 AS val UNION ALL
  SELECT +inf AS val -- <-- THIS doesnt work
)

SELECT 
  val,
  IS_INF(val) as is_infinity
FROM demo_tbl

in order to give desired output similar to this:

+---+-----+-------------+
|   | val | is_infinity |
+---+-----+-------------+
| 0 |   1 | False       |
| 1 | inf | True        |
+---+-----+-------------+

I searched the documentation as best as I could and had a Google around this but couldn't turn up an answer.

Upvotes: 7

Views: 6891

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

You can create the value using cast():

select is_inf(cast('Infinity' as float64))

or:

select is_inf(cast('inf' as float64))

Buried in the documentation is:

There is no literal representation of NaN or infinity, but the following case-insensitive strings can be explicitly cast to float:

  • "NaN"
  • "inf" or "+inf"
  • "-inf"

Upvotes: 5

Related Questions