converting from nvarchar to real datatype? 1

[jgoodman00]

I tried the following SQL to see if I could convert some data into REAL datatype on the fly (I eventually want to convert it permanently).

SELECT CAST(Distance AS REAL)
FROM ADOPT
ORDER BY Distance

However, when I try this I get an error (I am running this throught eh SQL Query Analyzer) Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to real.

So, I ran:
SELECT DISTINCT Distance
FROM ADOPT
ORDER BY Distance

This produced 690 results. The first is NULL. The second is 0, followed by several values such as 004 etc. The last value is 99

Are any of these values specified illegal for the REAL datatype? If not, what is the best way of tracing which value(s) are the illegal ones?

James Goodman

 

[Bernadette]

Hi There

Try looking up the CONVERT function in BOOKS ONLINE.
Sql Server will use an implicit conversion to convert a nvarchar to a real if you use the CONVERT function.

Syntax .............

CONVERT(real, DISTANCE)


Hope This helps.

Bernadette

 

[jgoodman00]

I seached bol for Convert & it came up with the CAST statement. It also said that these provide similar functionality. I tried this, but it still produces exactly the same error... James Goodman

 

[Bernadette]

Hi James

I tried the following myself. I set up a table called 'TABLE1' with one field called 'distance' which is a nvarchar.
I populated the 'distance' field as following ....

Distance
=========
100
100.5
text

I then ran the following in Query Analyser

select
convert(real, distance) as distance_r
from table1

It works fine for the first 2 but I get the same error as you when it tries to convert the text 'text' to a real as you cant convert actually alphabetic characters to a number.

May I suggest you look at the data populating the 'distance' field in your table to make sure that there is no bad data there that will prevent your function from working.

A little note
-------------
CAST is the same as CONVERT. CONVERT is older and much handier for converting dates, due to its style option where CAST is ansi-92 compliant.


Hope this works for you. Let me know how you get on.

Bernadette

 

[plantj]

This may help...
select
distance
,case
when patindex('%[^0-9]%',distance)>0 then
0
else
convert(real,distance)
end
from
adopt
where
distance<>null
and distance>''

 

[tlbroadbent]

You can use the IsNueric function to validate the date before converting or to find non-numeric data.

Query to Convert Nvarchar to Real:
SELECT
Case Isnumeric(Distance)
When 1 Then cast(Distance As Real)
Else 0 End As RealVal
FROM ADOPT
ORDER BY 1

Query to determine whicah values are not numeric:
SELECT Distance
FROM ADOPT
WHERE IsNumeric(Distance)=0 Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[jgoodman00]

thanks, i worked it out by good ol' trial & error in the end. I started providing a where clause & found the problem to be one instance or 32O (thats an O on the end, not a zero). grrrr James Goodman