February 20, 2009 at 2:33 am
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
February 20, 2009 at 2:49 am
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
February 20, 2009 at 3:07 am
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
February 20, 2009 at 4:38 am
Note that isnumeric is not always reliable
select isnumeric(','), isnumeric('12d6')
Failing to plan is Planning to fail
February 22, 2009 at 12:15 pm
[font="Verdana"]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.
[/font]
February 23, 2009 at 7:10 am
If are unsure of your data, you might identify the offensive values with something like this:
[font="Courier New"]SELECT whatever
FROM wherever
WHERE ISNUMERIC (whatever) = 0[/font]
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:
[font="Courier New"]SELECT whatever, CONVERT(float, whatever) AS fl_whatever
FROM wherever
WHERE ISNUMERIC (whatever) = 1[/font]
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.
February 23, 2009 at 12:42 pm
venki (2/20/2009)
Hi,I have a negative numbers in one of the column. Actually that is temperature min_temp.
Min_Temp floatIn 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 1Error 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
February 24, 2009 at 5:50 am
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"
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply