Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Incorrect data type in Float column Expand / Collapse
Author
Message
Posted Thursday, February 21, 2013 9:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, December 14, 2014 7:17 AM
Points: 7, Visits: 281
Somehow, I have a table with non-numbers in my floating column.

I can find them...
select isnumeric(Column) as IS_Numeric , convert(nvarchar (20),Column) as IS_Value
from TABLE
order by IS_Numeric

what i find is that there are values of " -1.#IND " in my column when the column is defined as FLOAT.


When I try to run a update where IS_Numeric = 0, i get the message "Possible index corruption detected. Run DBCC CHECKDB."

update TABLE
set COLUMN= 0
where (select isnumeric(Column) as IS_Numeric from TABLE )= '0'


Would appreciate the advice!


Cheers,
Mark
Post #1422664
Posted Thursday, February 21, 2013 10:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
Before proceeding I suggest that you read this short, very short article by Jeff Moden. (It hopefully will assist you in increasing your understanding of your problem)

http://www.sqlservercentral.com/articles/IsNumeric/71512/


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1422686
Posted Thursday, February 21, 2013 10:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, December 14, 2014 7:17 AM
Points: 7, Visits: 281
Ron - thanks for the link. I wasn't aware of that.

However, the ISNumeric query is working as I intend. I do get an overflow when trying to update those records where IS_Numeric = 0. Basically, i want to do an update either based on IS_Numeric=0 or Value= "-1.#IND". Either would work for me.

But I can't execute a straight query of the table. I need to CONVERT the float column to NVARCHAR to see the offending data.


Thanks again.



IS_Numeric Value
0 -1.#IND
0 -1.#IND
0 -1.#IND
0 -1.#IND
0 -1.#IND
0 -1.#IND
0 -1.#IND
1 8.57704
1 8.60668
1 8.61399
1 8.62084
1 8.62934
Post #1422698
Posted Thursday, February 21, 2013 3:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, December 14, 2014 7:17 AM
Points: 7, Visits: 281
**removed**
Post #1422820
Posted Wednesday, February 27, 2013 2:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, December 14, 2014 7:17 AM
Points: 7, Visits: 281
I had no primary key on the table and no guarantee of uniqueness on any given row. Because I was using the NaN column as part of the where clause, I would get an overflow.

In order to null the values, I first had to create a unique column (identity)

-- add idenity column
Alter Table dbo.TABLE
Add UniqueID Int Identity(1, 1)
Go


Now I can take the raw data converted into NVarChar as well as the IsNumeric value and put it into a temporary table.

-- select data into temp table
SELECT
convert(nvarchar (20), column1) as column1
,convert(nvarchar (20), column2) as column2
,convert(nvarchar (20), column3) as column3
,isnumeric(column1) as col1_Numeric
,isnumeric(column2) as col2_Numeric
,isnumeric(column3) as col3_Numeric
,[UniqueID]
INTO [TEMPORARYTABLE]
FROM [TABLE]
order by col1_Numeric asc


I can update the original table by identity column and NULL the NaN values .

--null the NaN data in Column1
Update A
set A.Column1 = null
From TABLE A, TEMPORARYTABLE B
Where A.UniqueID=B.UniqueID
And B.col1_Numeric=0

--null the NAN data in Column2
Update A
set A.Column2 = null
From TABLE A, TEMPORARYTABLE B
Where A.UniqueID=B.UniqueID
And B.col2_Numeric=0


And clean up


--drop identity column
Alter Table TABLE
Drop Column UniqueID
Post #1424775
Posted Wednesday, February 27, 2013 3:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, December 14, 2014 7:17 AM
Points: 7, Visits: 281
According to MSFT,

why we cannot use “where isnumeric(Column) = 0” to null the data::
The reason is “isnumeric(Column)” check is in condition which executes before the “select” statement. Since we got the index/heap corruption here, we could not use the problem column to find the row as a condition. However, converting in your select statement is not used for finding the problem row. For more information about the logical processing order of the select statement, please see http://msdn.microsoft.com/en-us/library/ms189499.aspx
Post #1424788
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse