Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# CAST and Isnumeric

Posted on 2004-10-12
Medium Priority
1,903 Views
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?
0
Question by:ratzephyr

LVL 70

Expert Comment

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
0

LVL 70

Expert Comment

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.
0

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?
0

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
0

LVL 32

Expert Comment

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
0

LVL 5

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
--------------------------------------

rgds,
Ajit Anand
0

LVL 7

Expert Comment

ID: 12326410
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

## Featured Post

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down â€¦
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then readingâ€¦
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
###### Suggested Courses
Course of the Month6 days, 15 hours left to enroll