Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Trybbe
Starting Member
27 Posts |
 Posted - 2008-04-10 : 05:06:54
|
Hi! Please help.I have 10 table which I need to merge into 1. The problem is the department field on one of the tables is nvarchar(255) while on the other tables is float. I have tried to use cast/convert and I still get error "Msg 8114, Level 16, State 5, Line 1Error converting data type nvarchar to float."I am using sqlserver 2005Please help  |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-04-10 : 05:09:30
|
If you want to convert nvarchar to float, you need to check whether there are values which are non-numeric.Select * from Table where col like '%[^0-9]%' or else convert from float to nvarchar.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
Trybbe
Starting Member
27 Posts |
Posted - 2008-04-10 : 05:39:55
|
| Thanks. I'm sorted now. There are non-numeric values in that field |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-04-10 : 05:55:41
|
quote:
Originally posted by Trybbe
Thanks. I'm sorted now. There are non-numeric values in that field
Thats why you should always use proper data type to store dataMadhivananFailing to plan is Planning to fail |
|
|
|
vurna78
Starting Member
3 Posts |
Posted - 2009-03-05 : 12:41:39
|
| Hi,This was a good post, I have the exact same problem as Trybbe. However I dont understand your answer Madhivanan. Do you mean that even if the source column contained only numeric values it would still be impossible to change the data type from nvarchar to float? And if so, is there no way at all in SQL server 2005 to change the data type of numbers(with decimals) stored as text to into a data type for numbers?I've tried a few different things, but SQL only gives me the error message "Error converting data type nvarchar to float.Mainly: CAST(scheme1.table1.[field_amount] AS float)Also tried: Cast(replace(YOUR_VARCHAR_COLUMN, ',','') as float) //As per TheDevil in this post: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=98218 However that made things even worse.Also I have viewed this table presented by Microsoft: http://msdn.microsoft.com/en-us/library/ms187928.aspx where you 4 pages down find an overview of how you should be able to convert data between different data types. (When I found this I thought I had struck gold! But sadly that was not the case.I have tried converting the column both thru explicit and implicit but it fails and returns "Error converting data type nvarchar to float" message to me.Any suggestions about this known / unknown issue in SQL Server 2005?Brg, Erik |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-05 : 12:57:13
|
| Madhi never meant you cant change a field to float it contain only numeric values. What he told was if you're sure it contains numeric values only, then why dont make column numerical type like int,float,... rather than declaring it as some character type and doing cast/converts all over the place. The error occurs not because it cant convert numerical data to int,float,... but because of presence of non numeric data which also it tries to convert. In such cases, what you need to do is to use where to filter out only values with numeric data and then cast/convert them which works fine. |
|
|
|
vurna78
Starting Member
3 Posts |
Posted - 2009-03-06 : 07:26:25
|
| Hey,Okay, so if I understand correctly the problem I have with not being able to convert numeric values in a nvarchar column to float is because I have chosen nvarchar in the first place thus I cannot convert this column to float?So in order to correct my problem I have to change the properties of the original nvarchar field to float?I have done query on the column to double check if I had anything other then numeric's by using a filter by "like '%[^0-9]%'" but it returned zero rows to me, so that would suggest I do not have any non numeric values in my field correct?The problem with my source table that I read from is that I have all fields in it as nvarchar. The table is my target table for imports from flat files I do regularly and I have had problems in the past if I import to numeric field properties so I always import everything as a text based data type and then convert.Apologies for posting in this forum, maybe the beginners forum would be better suited but I usually like to search for existing thread before I post an entirely new thread.Brg, Erik |
|
|
|
|
|
|
|
|
Data Conversion