How to select column values which throw an error if cast to a new type.

Question

Hi there,

I need an sql query to give me the following:

All the values in a column of type varchar(50) which will NOT convert or will throw an error if cast / converted to an int.

E.g.

row 1: '1'

row 2: '2'

row 3: '3a'

row 4: '4.5'

I need row 3....however there are tens of thousands of rows.

Thanks!

Answer 1

http://msdn.microsoft.com/en-us/library/aa933213%28SQL.80%29.aspx

e.g.,

SELECT * WHERE ISNUMERIC(column)=1

Edit: Actually, this wouldn't quite cut it if you want precisely int only. Try this instead. If you only want int range, then throw in a range check as well (feeling a bit lazy at this point).

SELECT * 
WHERE 
       ISNUMERIC(column)=1 
       OR (CEILING(CONVERT(float, column))!=FLOOR(CONVERT(float, column)))