What's triggering the error?
-
We're starting a new ETL process that will have data something like
DECLARE @scores TABLE (RepId int, score varchar(50))
INSERT @scores(repId, score)
SELECT 1, '5' UNION
SELECT 1, '8' UNION
SELECT 1, '7' UNION
SELECT 2, '6' UNION
SELECT 2, '.' UNION
SELECT 2, '9' UNION
SELECT 2, '9' UNION
SELECT 2, '7'
--We'll want to average the scores by RepId.
SELECT avg(cast(score AS int) * 1.0) FROM @scores
WHERE isnumeric(score) = 1 AND cast(score AS int) BETWEEN 1 AND 10
GROUP BY RepId
Yields an error due to the '.' in one of the scores. Yes, the data will be like this and obviously needs to be cleaned. I suspect the error is in the WHERE clause but even
WHERE isnumeric(score) = 1 AND score BETWEEN 1 AND 10
gives an error. Is this a candidate for a regular expression?
Thanks,
Ken
-
If you only want the rows where the score is between 1 and 10, I would just replace you where statement with something like this.
WHERE score like '[1-9]' or score like '10'
-
As per BOL: "ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($). For a complete list of currency symbols, see Using Monetary Data." Use another character (not dot)- everything should be fine
-
Have a look here
http://www.sqlservercentral.com/articles/IsNumeric/71512/
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Ah, NOT LIKE '%[^0-9]%'. That's the one. I'm using between 1 and 10 in my example but I don't know that's the range for every question. Maybe there will be a question like on a scale from 12 to 30 :-D. This covers all bases.
Thanks,
Ken
-
Read this article by Jeff Moden about the "ISNUMERIC" function and how it can result in returning a field is numeric, when it fact it is not, much akin to your problem
-
One other piece of discussion that noone's brought up yet.
WHERE isnumeric(score) = 1
This cannot be trusted to go first. It's quite possible to end up with conversion errors and the like from this. You need to subquery this where clause with a FORCE ORDER on the query to single statement it, or you need to temp table the restricted data set. Overloaded columns = significant problems.
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
Evil Kraig F (2/22/2012)
One other piece of discussion that noone's brought up yet.WHERE isnumeric(score) = 1
This cannot be trusted to go first. It's quite possible to end up with conversion errors and the like from this. You need to subquery this where clause with a FORCE ORDER on the query to single statement it, or you need to temp table the restricted data set. Overloaded columns = significant problems.
Right Evil Kraig. I also tried the more bland
WHERE isnumeric(score) = 1 AND score BETWEEN 1 AND 10
but that too gives an error. Guess an Int is conveyed in the BETWEEN clause
WHERE isnumeric(score)= 1 AND score BETWEEN '1' AND '9' works but who knows if it will return all valid cases.
Ken
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply