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

Negative numbers...... Expand / Collapse
Author
Message
Posted Friday, February 20, 2009 2:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 9:16 PM
Points: 146, Visits: 459
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?


Thanks a lot,
Venki
Post #661138
Posted Friday, February 20, 2009 2:49 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 40,615, Visits: 37,081
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 2008, MVP
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

Post #661144
Posted Friday, February 20, 2009 3:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 9:16 PM
Points: 146, Visits: 459
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.


Thanks a lot,
Venki
Post #661150
Posted Friday, February 20, 2009 4:38 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 23, 2014 5:59 AM
Points: 329, Visits: 470


Note that isnumeric is not always reliable
select isnumeric(','), isnumeric('12d6')




Madhivanan

Failing to plan is Planning to fail
Post #661190
Posted Sunday, February 22, 2009 12:15 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:12 PM
Points: 758, Visits: 1,031
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.
Post #662236
Posted Monday, February 23, 2009 7:10 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 10:51 AM
Points: 442, Visits: 938
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.
Post #662545
Posted Monday, February 23, 2009 12:42 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, December 15, 2014 1:42 PM
Points: 644, Visits: 2,151
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
Post #662906
Posted Tuesday, February 24, 2009 5:50 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:08 AM
Points: 2,365, Visits: 1,846
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"
Post #663380
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse