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

xpcnnkqh 于 2023-06-28 发布在其他

关注(0)|答案(3)|浏览(130)

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?

sql-server

来源：https://stackoverflow.com/questions/5789422/is-there-an-sql-condition-that-can-look-for-non-integers-in-a-column

3条答案

按热度按时间

eoigrqb61#

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

赞(0）回复(0）举报 2023-06-28

rkue9o1l2#

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*/

赞(0）回复(0）举报 2023-06-28

v8wbuo2f3#

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

赞(0）回复(0）举报 2023-06-28

我来回答

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

3条答案

相关问题

热门标签

最新问答