Updating the NaN value in the database
I have an old database that has a table containing columns ( dataType REAL ) with NaN (Not a Number) values . Now if I query these tables I get errors. So I tried the following query to update them:
UPDATE Table SET column = 0 WHERE IsNumeric(column)=0
But even this request could not complete the error
"returned invalid data for the column".
If the datatype of the column is varchar then I could easily update. But the column is REAL , so I cannot update the table. Help me, I am really stuck on this issue for two days.
source to share
I got the solution by converting this column value to string, then I can 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 NaN data in that column becomes -#@NaN
. So I was looking for this value in this column after converting to string. Hope this helps others.
Sorry, I don't have an old server version. I just have a backup.
Server new version : Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86) April 2, 2010 15:53:02 Copyright (c) Microsoft Corporation Enterprise Edition for Windows NT 6.1 (Build 7601: Service Pack 1)
source to share
Depending on exactly how (database versions and fixes) you got into this situation, the following statement released on your database will probably help you.
DBCC CHECKDB WITH DATA_PURITY
If that doesn't help, drop all indexes and constraints on the column and issue
UPDATE Table SET column = 0 WHERE IsNumeric(column)=0
again. Then re-create the indexes or constraints.
source to share