Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Incorrect data type in Float column


Incorrect data type in Float column

Author
Message
BiatchCakes
BiatchCakes
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 310
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
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5695 Visits: 25280
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
BiatchCakes
BiatchCakes
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 310
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
BiatchCakes
BiatchCakes
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 310
**removed**
BiatchCakes
BiatchCakes
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 310
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

BiatchCakes
BiatchCakes
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 310
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search