Possible to represent NaN in scientific notation?-CodePudding

Is there a way to represent NaN in SQL with some sort of 'special value' ? Or is it always necessary to use a cast from a string to represent it, for example:

SELECT CAST('NaN' AS FLOAT)
-- NaN

Is there any such value-construction I can use directly, such as:

SELECT 1.2345e-6789
-- NaN

CodePudding user response：

You should be able to use 0.0/0.0: IEEE 754 requires that the division of zero by zero to result in NaN.

CodePudding user response：

Null column values display as NaN

When you query the table using the same select statement in Databricks SQL, the null values appear as NaN.

select * from default.<table-name> where <column-name> is null

NaN is short for not a number. This is how null values are displayed in Databricks SQL.