CAST and Isnumeric

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?
ratzephyrAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
So this doesn't work?:


SELECT CAST(varcharColumn AS INT)
FROM yourTable
WHERE varcharColumn NOT LIKE '%[^0-9]%'
AND CAST(varcharColumn AS INT) >= 20
0
 
Scott PletcherSenior DBACommented:
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
0
 
Scott PletcherSenior DBACommented:
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.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
ratzephyrAuthor Commented:
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?
0
 
Brendt HessSenior DBACommented:
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
0
 
ajitanandCommented:
This query will do your work:
--------------------------------------
SELECT CAST(field1value AS INT) FROM myTable
WHERE ISNUMERIC(field1value) = 1 AND CAST(field1value AS INT) > 20
--------------------------------------

rgds,
Ajit Anand
0
 
sukumar_diyaCommented:
Hi ,
try this .....

SELECT CAST(Field1 AS INT) ,Field2,Field3
FROM (
            SELECT
                        Field1,
                        Field2,
                        Field3
           FROM    
                        YourTable
           WHERE
                        ISNUMERIC(Field1)=1
           )
WHERE CAST(Field1 AS INT) BETWEEN 10 and 1000


Hope this helps
Sukumar
0
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.

All Courses

From novice to tech pro — start learning today.