Is there an sql condition that can look for non integers in a column?

Question

Basically I would like a select statement that would function like this

SELECT *
FROM table  
WHERE column IS NOT INT  

Is there a condition like this or how do you check for non-integers in an nvarchar(10) column?

Fungosity

I deleted my answer as it was wrong... – Cranium

=( but at least you got +30 in score =) – Fungosity

Is '42.0' considered an integer? – Janice

(And a badge! stackoverflow.com/badges/37/disciplined) – Cranium

Good question, could you extend your answer to cover both alternatives? – Fungosity

What RDBMS? Are you only interested in positive integers? – Bassarisk

I actually only have integers from 1-1000 or letters from a-z. But if you could cover both 42.0 and -15 it would probably be of help to others with similar questions. – Fungosity

@Niklas: my query will match 42.0, -15 and 1E8. – Janice

RDBMS? I use SQL Server. – Fungosity

Answer 1

In SQL Server you can do:

SELECT  *
FROM    mytable  
WHERE   CASE WHEN IsNumeric(mycolumn) = 1 THEN CASE WHEN CAST(mycolumn AS FLOAT) <> CAST(CAST(mycolumn AS FLOAT) AS INT) THEN 1 END ELSE 1 END = 1

Janice

Sorry, I mean I get this error: An expression of non-boolean type specified in a context where a condition is expected, near 'END'. – Fungosity

This still gets errors for scientific notation (1e4 or 2d6) – Meditate

Answer 2

You could also use

SELECT  *
FROM    T 
WHERE  C = ''
        OR C LIKE '%[^0-9-]%' /*Contains a char other than - or 0-9*/
        OR C LIKE '_%-%'  /*Contains the - char other than 1st position*/

Bassarisk

Need to exclude zero length fields also I think – Toot

Hard to pick a correct answer here. Yours will work in my specific case but Quassnoi's covers a wider range =/ – Fungosity

I'd go for Quassnoi's as it is more general. Extending the LIKE to cover all cases would likely make it a fair bit more convoluted! – Bassarisk

@Alex - Yep. Adjusted to cope with that and -ve integers. – Bassarisk

Answer 3

This is so weird

SELECT * FROM TABLE WHERE Period_Year > 1900 AND Period_Month BETWEEN 1 AND 12

Fixed the problem.

the database has no value that are less or eqeul to 1900 and it has no months NOT BETWEEN 1 OR 12.

I also did a SELECT DISTINCT ISDATE(CONCAT( Period_Year,'/',Period_Month,'/', 1)) FROM TABLE

I GOT A TRUE i.e 1 BACK

NO FALSE i.e 0 WHERE RETURNED

only 1 row with 1 for true

Even when I do SELECT * FROM TABLE WHERE Period_Year > 1

this fixes the problem

Lone