Solved

Query  - show only records where a field has a numeric value AND the value is > 6

Posted on 2015-02-02
3
87 Views
Last Modified: 2015-02-02
I have a  field that can contain number or letters.  

I want to return records if the field contains a number AND the number is > 6

What would be the syntax?
0
Comment
Question by:vbnetcoder
3 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 40584874
select * from yourtable
where isnumeric(yourfield) != 0
and yourfield > 6
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 40584877
SELECT ...
FROM table_name
WHERE
    1 =
        CASE WHEN column_name LIKE '%[^0-9]%' THEN 0
                  WHEN column_name > 6 THEN 1
                  ELSE 0 END

You have two potential query-breaking issues here:
1) isnumeric() is unreliable for only ints because it has to check so many possible numeric formats:
SELECT ISNUMERIC('10E0'),ISNUMERIC('2,3444'),ISNUMERIC('4D1'),ISNUMERIC('$')

2) SQL can "re-arrange" conditions so unless you use CASE to force a specific order of comparison, SQL could get a non-numeric comparison even though you checked for numeric.

For example, even though you wrote:
WHERE column_name <matches the number format I want> and
    column_name > 6
SQL might treat it as:
WHERE (column_name > 6)  AND ....
and non-numeric values in column_name would cause an abend.
0
 

Author Closing Comment

by:vbnetcoder
ID: 40584910
ty
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

910 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

20 Experts available now in Live!

Get 1:1 Help Now