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


Negative numbers......


Negative numbers......

Author
Message
VRT
VRT
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 Visits: 603
Hi,

I have a negative numbers in one of the column. Actually that is temperature min_temp.

Min_Temp float



In another table, the datatype is varchar for this data. I want to transfer that varchar data to Float data. But in source, the negative numbers are there like -20, -30.8 ........

The transfer is failing and showing the error
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.



I used the CAST function to convert but not working.
Is there any way to fix this? Please tell me it is very urgent?

Thank You
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47427 Visits: 44405
Are you sure all the values in the column are numeric?

While it's not perfect, try using ISNUMERIC and see if there are any columns that it considers not numbers.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


VRT
VRT
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 Visits: 603
GilaMonster (2/20/2009)
While it's not perfect, try using ISNUMERIC and see if there are any columns that it considers not numbers.


Thanks GilaMonster.

It is due to some nonnumeric data I believe. I am not sure where the problem is. Because There are 5000 records.
The Min_Temp column values for some records is not null and an empty space. This might be the problem.

I checked with ISNUMERIC and if it is true convert to float else null.

Millions of thanks for your help. I become nervous because the live data is there and the total production affects if the transfer is not working properly.Thanks for your help.

Thank You
Madhivanan-208264
Madhivanan-208264
Old Hand
Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)

Group: General Forum Members
Points: 337 Visits: 476
Note that isnumeric is not always reliable
select isnumeric(','), isnumeric('12d6')



Madhivanan

Failing to plan is Planning to fail
Bruce W Cassidy
Bruce W Cassidy
Right there with Babe
Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)

Group: General Forum Members
Points: 787 Visits: 1033
Does the SQL Server error message tell you what the value is that it's failing to insert?

This looks like a fault with your data feed. You are trying to insert a non-numeric value into your number... it just won't work.

J-440512
J-440512
SSC-Addicted
SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)

Group: General Forum Members
Points: 447 Visits: 949
If are unsure of your data, you might identify the offensive values with something like this:

SELECT whatever
FROM wherever
WHERE ISNUMERIC (whatever) = 0


Since it takes just one single bad apple to crash everything, you just might get lucky and and only have a few to fix.

You could also do something like this:

SELECT whatever, CONVERT(float, whatever) AS fl_whatever
FROM wherever
WHERE ISNUMERIC (whatever) = 1


But if you limit yourself to that, you do not know what else is left behind. I hate loose ends left dangling. Better to deal explicitely with the exception.
GabyYYZ
GabyYYZ
Say Hey Kid
Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)

Group: General Forum Members
Points: 673 Visits: 2332
venki (2/20/2009)
Hi,

I have a negative numbers in one of the column. Actually that is temperature min_temp.

Min_Temp float



In another table, the datatype is varchar for this data. I want to transfer that varchar data to Float data. But in source, the negative numbers are there like -20, -30.8 ........

The transfer is failing and showing the error
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.



I used the CAST function to convert but not working.
Is there any way to fix this? Please tell me it is very urgent?


Before you cast/import, try this. I had an issue where there was data being imported from an external source (generated by SAP/Oracle) that had some hidden control characters in it and spaces as well. You could try something like:


select * from Source_Table where VarCharColumn like '%[^0-9.-]%' -- make sure '.' comes before '-'



The ^ means show me anything not a number, decimal point, or negative sign. I've noticed that whenever there is a char/varchar column that holds numeric values, some non numeric values inevitably get inside (such as spaces and end of line characters). Once the select statement returns the affected rows, hopefully not too many, you can update them manually which is what I do.

Here's a setup you can do to test this:


create table Source_Table
(
val varchar(10)
)

insert into Source_Table values('100.00')
insert into Source_Table values('101.01' + char(4)) -- char(4) picked arbitrarily, you can use anything under 32
insert into Source_Table values(char(4) + '102.02')
insert into Source_Table values('-103.03')
insert into Source_Table values('104.04 ') -- space is here

select * from Source_Table
where val like '%[^0-9.-]%' -- make sure '.' comes before '-'

-- drop table Source_Table



Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein

ChiragNS
ChiragNS
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2409 Visits: 1865
You also need to fix the issue while u r inserting data into the varchar column. You need to make sure that numeric data is being inserted/updated into that column.

"Keep Trying"
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