Select rows that can't be casted

sql-server, t-sql

Solution

Take a look at IsNumeric, IsInt, IsNumber, you can't use just isnumeric it will return true for - signs and other stuff like that

For example, this returns 1

SELECT ISNUMERIC('2d5'),
       ISNUMERIC('+')

Problem

In a column of my table are stored the number of the house address. Unfortunately my previous colleagues were not a fan of thinking so they made the column of type `varchar` and did not block input on the software... so now I'm stuck with a bunch of rows where the number of house/apartment is "N.I.", "Not Info", "Unknown", etc. instead of a meaningful number... I would like to select only the rows that are not numbers... something like `select * from table where CAST(column as int)` throws exception

Original source