CODEWITHSUNDEEP
sql-servert-sql
Leonardo
Leonardo

Reputation: 11391

Select rows that can't be casted

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

Upvotes: 3

Views: 583

Answers (4)

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Try this:

SELECT * FROM table WHERE ISNUMERIC(column + 'e0') = 0

Upvotes: 1

SQLMenace
SQLMenace

Reputation: 135001

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('+')

Upvotes: 7

jfin3204
jfin3204

Reputation: 709

Create a function that that tries the cast and any other logic that you needs then return 0 if the value doesn't meet your requirements if it succeeds return 1. then use the function in the where clause

Upvotes: -1

bummi
bummi

Reputation: 27377

select * from table where ISNumeric(column)=0

but it may give false positives .....

Upvotes: 3

Related Questions