Menu
how can I accomplish:
select * from table where column_value is int
I know I can probably inner join to the system tables and type tables but I'm wondering if there's a more elegant way.
Note that column_value is a varchar that "could" have an int, but not necessarily.
Maybe I can just cast it and trap the error? But again, that seems like a hack.
710
To check if the given value is a string or not ,we use the cast() function. If the value is not numeric then it returns 0, otherwise it will return the numeric value. In this way, we can check whether the value is an integer or not.
The ISNUMERIC() function tests whether an expression is numeric. This function returns 1 if the expression is numeric, otherwise it returns 0.
The int data type is the primary integer data type in SQL Server. The bigint data type is intended for use when integer values might exceed the range that is supported by the int data type.
In SQL Server, we can use the ISNUMERIC() function to return numeric values from a column. We can alternatively run a separate query to return all values that contain numeric data.
select * from table
where column_value not like '[^0-9]'
If negative ints are allowed, you need something like
where column_value like '[+-]%'
and substring(column_value,patindex('[+-]',substring(column_value,1))+1,len(column_value))
not like '[^0-9]'
You need more code if column_value can be an integer that exceeds the limits of the "int" type, and you want to exclude such cases.
159
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
