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

2 answers

I got the solution by converting this column value to string, then I can update it.

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


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)



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



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.



All Articles