SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 nchar to real or to something I can get AVG from
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

barnabeck
Posting Yak Master

Spain
194 Posts

Posted - 10/11/2012 :  11:09:10  Show Profile  Reply with Quote
I have a table that is fed by an access database; the columns format unfortunatly has to be nchar and I can't find the way to calculate an AVG, while min(), max() works on that fields.

AVG(CAST(Temperature as real)) -> Error converting data type nvarchar to real

Martin

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/11/2012 :  11:39:42  Show Profile  Reply with Quote
You should be able to do this unless there is some data that is non-numeric. Typically, I have seen this happen when the data has thousands separators (3,745.33 etc.).

You can see if there is non-numeric data using this query
SELECT *  FROM YourTable WHERE ISNUMERIC(Temperature) = 0;
But, ISNUMERIC is not 100 percent accurate for this purpose. For example, it won't catch the comma's that I referred to earlier.
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 10/11/2012 :  11:41:03  Show Profile  Reply with Quote
Let's go with the easy answer first. Do either of these produce any records?

SELECT *
FROM yourTable
WHERE Temperature like '%,%'

SELECT *
FROM yourTable
WHERE Temperature like '%[^0-9]%' and Temperature not like '%.%'

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

barnabeck
Posting Yak Master

Spain
194 Posts

Posted - 10/11/2012 :  11:49:49  Show Profile  Reply with Quote
Thank you Jim, the komma got me on the right track... and even sunitabeck had the right hint in her answer, although I didn't question the numeric content until Jim's query ckecked the komma...

AVG(CAST(Replace(Temperature,',','.') as real)) does the tric

Martin
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 10/11/2012 :  12:04:56  Show Profile  Reply with Quote
Like Sunita said, ISNUMERIC isn't very unreliable, and I know Europe gets their commas and decimals backwards!

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/11/2012 :  12:12:06  Show Profile  Reply with Quote
Good point about Europeans using comma and period for purposes opposite to what US does!

Sometimes, I put all the characters that I want to allow into the like clause - like this:
SELECT CASE WHEN
	col LIKE '%[^0-9. -E]%' THEN 'Nonnumeric' ELSE 'Numeric' END;
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 10/11/2012 :  12:41:26  Show Profile  Reply with Quote
Nice! I'm adding that to my UsefulThings folder!

Thanks,

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000