CODEWITHSUNDEEP
sqlsql-serverintwhere-clause
Troy Wheeler
Troy Wheeler

Reputation: 3

where not dataype sql

I'm trying to filter some data - I have a column which looks like it is mainly smallint/int. Is there anyway I can run a where statement to say where not int or where not small int??

Microsoft SQL Server manager.

Upvotes: 0

Views: 58

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82534

If you want a where clause that can tell you if the column contain information that can't be converted to int or smallint, you can use try_cast:

SELECT *
FROM <TableName>
WHERE TRY_CAST(<ColumnName> AS Int) IS NULL

You can change the int to smallint to get values that can't be converted to smallint but might be convertible to int.

Don't forget to replace <TableName> and <ColumnName> to the names of the relevant table and column.

The Try_Cast built in function will return null if the value in <ColumnName> is null or if it can't be converted to int (and since all smallint values can also be converted to int, it also can't be converted to smallint).

Upvotes: 1

Related Questions