Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 103
  • Last Modified:

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

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
vbnetcoder
Asked:
vbnetcoder
1 Solution
 
sdstuberCommented:
select * from yourtable
where isnumeric(yourfield) != 0
and yourfield > 6
0
 
Scott PletcherSenior DBACommented:
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
 
vbnetcoderAuthor Commented:
ty
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now