## Convert string to decimal then average.

 Author Message DC-850296 Forum Newbie Group: General Forum Members Points: 9 Visits: 31 I need to convert a string to a decimal then average the results while ignoring null or "non-numeric like" values.[color=CC0000]With this query, I can return the average but only if I convert null or strings other than 1-5 to "0".AVG(CONVERT(decimal, CASE WHEN substring(hd1.workitem_comment, 115, 1) IN ('1','2','3','4','5') THEN substring(hd1.workitem_comment, 115, 1) ELSE '0' END)) AS 'Handled'[/color]But with this one, I get an "error converting data type nvarchar to numericAVG(CONVERT(decimal(18,2), substring(hd1.workitem_comment, 115, 1))) AS 'Handled' Any idea how to accomplish this? JuncTheMayl SSC Eights! Group: General Forum Members Points: 948 Visits: 490 you've got the meat of it there... instead of doing the filtering in your avg() function, do it in the WHERE clause:AVG(CONVERT(decimal(18,2), substring(hd1.workitem_comment, 115, 1))) AS 'Handled' ...WHERE substring(hd1.workitem_comment, 115, 1) IN ('1','2','3','4','5') DC-850296 Forum Newbie Group: General Forum Members Points: 9 Visits: 31 That works great, Lenny. But one issue...how do I account for Null values. I don't want them to be included in the average. I'm not sure how to ignore those...that's sort of how I got to the initial query statement. JuncTheMayl SSC Eights! Group: General Forum Members Points: 948 Visits: 490 you can add IS NOT NULL to the clause:WHERE substring(hd1.workitem_comment, 115, 1) IN ('1','2','3','4','5') AND hd1.workitem_comment IS NOT NULLor, if that doesn't work the way you want, if you can post some samples, i can give it another shot...edit: just thought of something else, too:WHERE substring(ISNULL(hd1.workitem_comment, ''), 115, 1) IN ('1','2','3','4','5')