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

Question

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.

SELECT * 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?

score 3 · Accepted Answer · answered Sep 09 '11 at 09:50

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.

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

1 Answers1