SQL Server, where field is int?

sql sql-server-2005 tsql entity-attribute-value
Scott Klarenbach picture Scott Klarenbach · Sep 4, 2009 · Viewed 11.4k times · Source

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.

Answer

Steve Kass picture Steve Kass · Sep 4, 2009 
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.

Related questions

Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
SQL query to select dates between two dates

I have a start_date and end_date. I want to get the list of dates in between these two dates. Can anyone help me pointing the mistake in my query. select Date,TotalAllowance from Calculation where EmployeeId=1 and Date …

Read More
How can I do an UPDATE statement with JOIN in SQL Server?

I need to update this table in SQL Server with data from its 'parent' table, see below: Table: sale id (int) udid (int) assid (int) Table: ud id (int) assid (int) sale.assid contains the correct value to update ud.…

Read More