nchar to real or to something I can get AVG from

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

nchar to real or to something I can get AVG from

Author

Topic

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-10-11 : 11:09:10

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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-11 : 11:39:42

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.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-10-11 : 11:41:03

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

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-10-11 : 11:49:49

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

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-10-11 : 12:04:56

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-11 : 12:12:06

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;

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-10-11 : 12:41:26

Nice! I'm adding that to my UsefulThings folder!

Thanks,

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources