varchar to int scenario

Question

varchar to int scenario

sqlguy-736318

Hall of Fame

Points: 3859
More actions
January 12, 2014 at 4:43 pm

#396094

I have an incident table with a column named ticketnumber. Ticketnumber has a varchar data type but I want to select the rows that have a ticketnumber int value > 15000. I tried the following query which appeared correct but it threw a cast exception:
select ticketnumber from incident
where isnumeric(ticketnumber) and
cast(ticketnumber as int) > 15000
So essentially, isnumeric doesn't filter out ticketnumbers that are not convertible to int before the cast to int is called. What's the simplest and most straightforward way to write this query for what I'm trying to achieve?

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

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

sqlguy-736318 (1/12/2014)
I have an incident table with a column named ticketnumber. Ticketnumber has a varchar data type but I want to select the rows that have a ticketnumber int value > 15000. I tried the following query which appeared correct but it threw a cast exception:
select ticketnumber from incident
where isnumeric(ticketnumber) and
cast(ticketnumber as int) > 15000
So essentially, isnumeric doesn't filter out ticketnumbers that are not convertible to int before the cast to int is called. What's the simplest and most straightforward way to write this query for what I'm trying to achieve?

I cover this in my Common TSQL Mistakes SQL Saturday session. Key Point: the optimizer can do almost anything it wants with your query as long as Boolean and algebraically it has the same effect or output. So here it is chosing to do the CAST before the ISNUMERIC.

Try this for your WHERE clause. This takes advantage of the "usually-short-circuiting" nature of the awesome CASE statement.

select COUNT(*) --see how many there are before trying to spit them all out to SSMS

from incident

WHERE CASE WHEN ticketnumber NOT LIKE '%[^0-9]%'

THEN CAST(ticketnumber AS INT)

END > 15000;

NOTE: your query can STILL blow up here if you try to CAST something that is too big to be an int. If it does, try a big_int or other numeric type.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

sqlguy-736318 Hall of Fame Points: 3859 More actions · Answer 2

Thanks Kevin - Why did you write "select count(*)" instead of "select ticketnumber" in your query?

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

Read my comment - it says why. I didn't want you trying to bring back a billion rows and crashing the machine you were running the query from. 😎

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

pietlinden SSC Guru Points: 63501 More actions · Answer 4

"I cover this in my Common TSQL Mistakes SQL Saturday session..." (just not #272? Not coming to NashVegas?? Drat!

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

pietlinden (1/12/2014)
"I cover this in my Common TSQL Mistakes SQL Saturday session..." (just not #272? Not coming to NashVegas?? Drat!

I did the Common TSQL Mistakes session on Nashville in 2010 - you can download the stuff from there.

I am still hoping to get into this Saturday's event - they have a number of blanks in the schedule. I live just 2 hours south, so I could be up there and present even with same-day selection. 🙂

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service