asked on

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?

Scott Pletcher

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

Scott Pletcher

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.

ratzephyr

ASKER

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?

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Brendt Hess

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

ajitanand

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

sukumar_diya

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