CAST and Isnumeric

Posted on 2004-10-12
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 69

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 69

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?
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

LVL 69

Accepted Solution

Scott Pletcher earned 500 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

615 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