February 21, 2013 at 9:13 am
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
February 21, 2013 at 10:03 am
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)
February 21, 2013 at 10:16 am
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
18.57704
18.60668
18.61399
18.62084
18.62934
February 21, 2013 at 3:53 pm
**removed**
February 27, 2013 at 2:56 pm
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
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
February 27, 2013 at 3:16 pm
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply