Convert NaN values to NULL, or NaN to 0 in SQL

Question

I have values in registries that contain NaN values (they come from MATLAB). How would I convert them to NULL, or preferably, 0, in SQL?

Answer 1

If you have imported them into a SQL Server table

update tbl
set theColumn = NULL
WHERE theColumn = 'NaN'

This converts the column values to NULL if they are the string 'NaN' For the column to contain 'NaN', it must be varchar, so if you prefer 0, that would be the string '0'

update tbl
set theColumn = '0'
WHERE theColumn = 'NaN'

In a select statement, you can use either of

SELECT NULLIF(theColumn, 'NaN') as NullifiedTheColumn, ...
SELECT CASE theColumn when 'NaN' then '0' else theColumn end as ZeroedTheColumn, ...

Answer 2

SELECT NULLIF('NaN', ColumnName)

example

DECLARE @d VARCHAR(3) ='NaN'

SELECT NULLIF('NaN', @d)

for 0

DECLARE @d VARCHAR(7) ='NaN'

SELECT CASE WHEN @d = 'NaN' THEN '0' ELSE @d END

Answer 3

How about CASE WHEN IsNumeric(column)=0 THEN 0 ELSE column?