Solved

CAST and Isnumeric

Posted on 2004-10-12
10
1,887 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:ScottPletcher
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:ScottPletcher
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 69

Accepted Solution

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now