Sql parse column as int and check if it is within range

I've used the top voted answer from here to check if a cell value is an integer. But I also need to do a range check on it.

SEL开发者_如何学CECT * FROM Table
WHERE (dbo.IsInteger(FieldName) = 1) AND FieldName > 400 AND FieldName < 500

But this returns a conversion error, as some of the cells in the column contains text. So, is it possible to get a subset, that is only results from the IsInteger query, and then do a range check on the result?

You need to wrap it in a CASE expression. I've altered the query slightly to use BETWEEN to avoid having to repeat the expression.

SELECT * FROM Table
WHERE CASE WHEN dbo.IsInteger(FieldName) = 1 
           THEN FieldName END BETWEEN 401 AND 499

The result of the expression will be NULL when dbo.IsInteger(FieldName) <> 1 which will not match the BETWEEN predicate.

Of course this is completely unsargable but so is your original query.