Finding Varchar Field - SQL Server Forums

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

Finding Varchar Field

Author

Topic

towardabettercountry
Starting Member

26 Posts

Posted - 2012-07-02 : 17:04:02

I've been searching all afternoon in vain! I'm getting the following error:

Error converting data type varchar to numeric

Below is the query. I've tried...
WHERE Exam_Score NOT LIKE '%[^0-9.-]%'

I've also tried...
WHERE AND Exam_Score NOT LIKE '%[^0-9.-]%'

But I can't find anything. Can anyone help??

--------------------------

DECLARE @TodayLastYear AS Date
SET @TodayLastYear = (SELECT dateadd(yy,-1,getdate()))

DECLARE @ProgramVersionCode varchar(25)
SET @ProgramVersionCode = 'DPT-2014'

SELECT Exam.Exam_Code
,AVG(CAST(Exam.Exam_Score AS Decimal(8,2))) AS Average_Exam_Score

FROM Enr JOIN Exam ON Enr.Student_ID = Exam.Student_ID
JOIN Changes ON Enr.Enr_ID = Changes.Enr_ID

WHERE Program_Version_Code_Enr = @ProgramVersionCode
AND Student_New_School_Status IN ('Accept', 'Conditional Accept', 'Future Start')
AND Start_Date_Student_Status <= @TodayLastYear
AND End_Date_Student_Status >= @TodayLastYear
AND Exam._Exam_Code IN ('GREVRBL', 'GREQAN', 'GREANA', 'PTCUMGPA', 'PTSCIGPA')
--AND Exam_Score NOT LIKE '%[^0-9.-]%'
--AND ISNUMERIC(Exam_Score) <> 1

GROUP BY Exam.Exam_Code

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-07-02 : 17:45:22

Try this first
WHERE ISNUMERIC(Exam_Score) = 0
Those values cannot be converted to numeric

Jim

Everyday I learn something that somebody else already knew

towardabettercountry
Starting Member

26 Posts

Posted - 2012-07-02 : 17:49:16

quote:
Originally posted by jimf

Try this first
WHERE ISNUMERIC(Exam_Score) = 0
Those values cannot be converted to numeric

Jim

Everyday I learn something that somebody else already knew

Thanks, Jim. I tried that but I don't get any results. (I actually meant to include that code in my original post but pasted the wrong line). Any other thoughts? It certainly seems strange...

towardabettercountry
Starting Member

26 Posts

Posted - 2012-07-02 : 17:53:57

I don't know if it helps, but here are some sample results:

Exam_Code Average_Exam_Score
PTSCIGPA 3.08
PTCUMGPA 3.21
GREANA 4.0
GREVRBL 380
GREQAN 620
PTCUMGPA 3.51

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-07-02 : 18:19:05

Then there's something in there greater than 2 decimal places or more than 999999. Probably the former. Try this
select * from exam_score where exam_score like '%e%' (isnumeric('1e7') = 1, but it breaks when you actually try to convert it)

if that does nothing, try

AVG(CAST(Exam.Exam_Score AS float)) AS Average_Exam_Score

If that works, then you have a float in there somewhere, and you'll have to convert it to a float and then round it to 2 decimal places, and then convert it to decimal(8,2) !

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