Solved

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

Posted on 2015-02-02
3
95 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
[X]
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
3 Comments
 
LVL 74

Expert Comment

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

Accepted Solution

by:
Scott Pletcher 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

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

756 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