Search code examples
pythonsqlitenullnan

numpy.nan_to_num equivalent in sql?

I am trying to rewrite a set of python operations in SQL. Specifically, I have a python script that queries from a sqlite3 connection from a table table_name for the column vals and note that vals is a nullable column.

It then filters the return values using numpy.nan_to_number which converts None->0 (NULL in sql) and large numbers to infinity.

Is there an equivalent way to do this as a SQL statement?

Solution

  • The standard approach for handling NULLs would be to use coalesce:

    SELECT COALESCE(vals, 0)
FROM ...

    Turning your large numbers into infinity is straightforward.

    Let a "large" integer be a value with "more than two digits", and let "infinity" be 99999. Then you'll want SELECT ... CASE WHEN vals > 99 THEN 99999 ELSE vals END. Feel free to substitute a FP value if that better fits your use case.