[Webinar] Streamline your web hosting managementRegister Today

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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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