Convert from nvarchar to Real? 1

[jgoodman00]

I am in the process of inserting data from an Access table into an SQL table. I have imported the relevant tables into Access. It has converted certain fields into nvarchar. I need to convert these fields into real datatype.

I wrote the following SQL to accomplish this:
UPDATE ADOPT1
SET [Chainage] = CONVERT(real, [Chainage])

However, upon execution I get the following error:
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to real.



Is there anyway of making this error message more useful, such as telling me exactly which value it cannot convert, so I can address the problem?
Is there an alternative?? James Goodman

http://www.james-goodman.co.uk

 

[AWithers]

I think you may be missing something fundeamental. Is the data type of your field chainage a real? I am guessing not and thus the update wont work

What you might need to do is create a new field on you table adopt1 of data type real (fieldname Newchainage)
then run the update you have created

e.g.
UPDATE ADOPT1
SET [NewChainage] = CONVERT(real, [Chainage])


then drop the chainage column and rename the newchainage column chainage

Hope this helps

Andy

 

[tlbroadbent]

You can Alter the column and change the data type to Real. If all the values are numeric, SQL Server will handle the conversion automatically. Make sure you check SQL BOL for restrictions on Altering columns.

You can list rows with non-numeric data in that column using the IsNumeric function in a select query.

Select * From ADOPT1
Where IsNumeric(Chainage)=0

You could even modify the non-numeric rows. In this example, the value of chainage would be changed to 0 if the value is not numeric.

Update ADOPT1
Set Chainage='0'
Where IsNumeric(Chainage)=0 Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.