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
Solved

CAST and Isnumeric

Posted on 2004-10-12
10
1,895 Views
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?
0
Comment
Question by:ratzephyr
10 Comments
 
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
0
 
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.
0
 

Author Comment

by:ratzephyr
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 69

Accepted Solution

by:
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
0
 
LVL 32

Expert Comment

by:bhess1
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

by:ajitanand
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

by:sukumar_diya
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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 ?
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.

790 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