CODEWITHSUNDEEP
sqldatabasenanzero
RMi Flores
RMi Flores

Reputation: 377

How to replace NaN with 0 in SQL

I'm trying to take the average and median of several values but they are coming out incorrectly because the values I have in the table that are supposed to be 0 are listed as NaN in the database.

Is there a way I can replace all instances of NaN with 0 so that I can fix my average and median values?

Upvotes: 3

Views: 13707

Answers (1)

jonju
jonju

Reputation: 2736

ISNUMERIC is what you are looking for

Select case when ISNUMERIC(ColumnName)=1 then ColumnName else 0 end

This query will replace all ColumnName which is not a number

To calculate average. Try this query

Select 
AVG(Case When ISNUMERIC(ColumnName)=1 Then ColumnName Else 0 End) As [Average]
From Tablename

Upvotes: 1

Related Questions