CAST and Isnumeric

Posted on 2004-10-12
Medium Priority
Last Modified: 2008-02-01
I need to query a varchar and cast it as an integer so I can sort and restrict the query by doing a greater than.

I can use the cast but it erors out if there are things in there that cannot be converted.  How do I use IsNumeric with Cast or Convert?
Question by:ratzephyr
LVL 70

Expert Comment

by:Scott Pletcher
ID: 12290939
Here is how to use ISNUMERIC(), although you shouldn't for this task (more on that in a second ...):

SELECT CAST(varcharColumn AS INT)
FROM yourTable
WHERE ISNUMERIC(varcharColumn) = 1
LVL 70

Expert Comment

by:Scott Pletcher
ID: 12290978
ISNUMERIC() allows several different numeric types, so it is not good for checking just for integer.  Do this instead:

SELECT CAST(varcharColumn AS INT)
FROM yourTable
WHERE varcharColumn NOT LIKE '%[^0-9]%'

The LIKE checks for any character that is *not* 0 thru 9.  If it finds one, the LIKE is true.  Then the NOT reverses the true to false.  That is, the NOT LIKE above will return true if a character *not* between 0 and 9 is found; it will return false if it is.  Since we are checking for integer values only, that is what we want.

Author Comment

ID: 12291493
When I do a greater than 20  ( >= '20') it returns numbers like 3 and 30 and 5000 but not all numbers that are over 20.

What next?
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

LVL 70

Accepted Solution

Scott Pletcher earned 2000 total points
ID: 12291716
So this doesn't work?:

SELECT CAST(varcharColumn AS INT)
FROM yourTable
WHERE varcharColumn NOT LIKE '%[^0-9]%'
AND CAST(varcharColumn AS INT) >= 20
LVL 32

Expert Comment

by:Brendt Hess
ID: 12293376
You might also use the first SELECT as a derived table, and then check, e.g.:

Select * From (SELECT CAST(varcharColumn AS INT) as NumValue
FROM yourTable
WHERE varcharColumn NOT LIKE '%[^0-9]%') x
WHERE NumValue >= 20

Expert Comment

ID: 12306873
This query will do your work:
SELECT CAST(field1value AS INT) FROM myTable
WHERE ISNUMERIC(field1value) = 1 AND CAST(field1value AS INT) > 20

Ajit Anand

Expert Comment

ID: 12326410
Hi ,
try this .....

SELECT CAST(Field1 AS INT) ,Field2,Field3
WHERE CAST(Field1 AS INT) BETWEEN 10 and 1000

Hope this helps

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

587 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question