Find all rows in a VARCHAR that can't be cast into an INT

Question

Find all rows in a VARCHAR that can't be cast into an INT

Shawn Therrien

Ten Centuries

Points: 1193
More actions
February 9, 2009 at 5:11 pm

#134536

I have a field that is a VARCHAR, though maybe 80% of the fields are INT values.
How would I get all the values that can't be cast into an INT?
Basically if I tried this:
SELECT * FROM TestScores TS WHERE TS.Grade > 50
-- OR
UPDATE TestScores SET Passed = (CASE WHEN TS.Score > 50 THEN 1 ELSE 0 END)
I get a type conversion error converting "30--P" into an INT (of course) and the whole transaction is rolled back.
The best I can see is to cursor through the list and try one at a time to fill a field I added called "Passed". In the end, all values that were NULL would be those that couldn't be cast into an INT and I could select that list to pass onto others who would find out what all the invalid scores meant.
This will work, but I always try to find a non-cursor way to do this if possible. I'm stumped. Is there another way to handle this?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 1

What is it that you really want to do? Do you just want to return a list of items that can't be converted to an INT or do you want to remove all but the numeric portion of a grade?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Shawn Therrien Ten Centuries Points: 1193 More actions · Answer 2

I need a list of everything that can't be converted to INT.

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 3

[font="Verdana"]

You could try using something like the following:

select *,

case

when isnumeric(Score) = 0 then 'Invalid'

when cast(Score as int) > 50 then 'Pass'

else 'Fail'

end as [Status]

from TestScores;

To just get a list of everything that's invalid:

select *

from TestScores

where isnumeric(Score) = 0;

[/font]

Shawn Therrien Ten Centuries Points: 1193 More actions · Answer 4

SELECT * FROM TestScores WHERE Score <> '0' AND ISNUMERIC(Score) = 0

Worked perfectly.

Thank you sir. Always a good day when I learn how to do away with an unnecessary cursor 🙂

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 5

Shawn Therrien (2/9/2009)
SELECT * FROM TestScores WHERE Score <> '0' AND ISNUMERIC(Score) = 0
Worked perfectly.
Thank you sir. Always a good day when I learn how to do away with an unnecessary cursor 🙂

You think so now... try adding a phat-phingered "3d2" to your score column and see if it works correctly. 😉 ISNUMERIC should never be used as an "ISALLDIGITS" test because it was never intended to be so. What you need to do is...

SELECT * FROM TestScores WHERE Score <> '0' AND Score NOT LIKE '%[^0-9]%'

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)