Search code examples
sqlsql-serverentity-attribute-value

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.

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?

Solution

  • 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.