Updating the NaN value in a DataBase

Question

I have a Old database in which there is a table containing columns(dataType REAL) with NaN( Not a Number) values. Now if I query on these tables then I am getting errors. So to update these I tried the following query:

UPDATE Table SET column = 0 WHERE IsNumeric(column)=0

But even this query failed throwing error

"returned invalid data for column ".

If the datatype of the column is varchar then I could have easily updated. But the Column is REAL so I am not able to update the table. Help me I am really stuck with this issue for two days.

Answer 1

I got the solution, by converting that column's value to string then I am able to update it.

UPDATE Table
SET Column = CASE
               when charindex('NaN', UPPER(Column)) > 0 then 0
               ELSE Column
             END 

Here, I am looking for the charindex of 'NaN' since when I do CONVERT(varchar(1000), Column) the data NaN in that column became -#@NaN. So I am looking for that value in that column after converting to string. Hope this helps for others.

Sorry I don't have the old server version. I just have its backup.

New Server Version: Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86) Apr 2 2010 15:53:02 Copyright (c) Microsoft Corporation Enterprise Edition on Windows NT 6.1 (Build 7601: Service Pack 1)

Answer 2

Depending on how exactly (database versions and hotfixes) you got into this situation, the following statement, issued in your database, will probably help you.

DBCC CHECKDB WITH DATA_PURITY

If this does not help, drop all indexes and constraints on the column and issue

UPDATE Table SET column = 0 WHERE IsNumeric(column)=0

again. Then recreate the indexes or constraints.