How can I query 'between' numeric data on a not numeric field?

I've got a query that I've just found in the database that is failing causing a report to fall over. The basic gist of the query:

Select *
From table
Where IsNull(myField, '') <> ''
And IsNumeric(myField) = 1
And Convert(int, myField) Between @StartRange And @EndRange

Now, myField doesn't contain numeric data in all the rows [it is of nvarchar type]... but this query was obviously designed such that it only cares about rows where the data in this field is numeric.

The problem with this is that T-SQL (near as I understand) doesn't shortcircuit the Where clause thus causing it to ditch out on records where the data is not numeric with the exception:

Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the nvarchar value '/A' to data type int.

Short of dumping all the rows where myField is numeric into a temporary table and then querying that for rows where the field is in the specified range, what can I do that is optimal?

My first parse purely to attempt to analyse the returned data and see what was going on was:

Select *
From (
   Select *
   From table
   Where IsNull(myField, '') <> ''
   And IsNumeric(myField) = 1
) t0
Where Convert(int, myField) Between @StartRange And @EndRange

But I get the same error I did for the first query which I'm not sure I understand as I'm not converting any data that shouldn't be numeric at this point. The subquery should only have returned rows where myField contains numeric data.

Maybe I need my morning tea, but does this make sense to anyone? Another set of eyes would help.

Thanks in advance

Solution

IsNumeric only tells you that the string can be converted to one of the numeric types in SQL Server. It may be able to convert it to money, or to a float, but may not be able to convert it to an int.

Change your

IsNumeric(myField) = 1

to be:

not myField like '%[^0-9]%' and LEN(myField) < 9

(that is, you want myField to contain only digits, and fit in an int)

Edit examples:

select ISNUMERIC('.'),ISNUMERIC('£'),ISNUMERIC('1d9')

result:

----------- ----------- -----------
1           1           1

(1 row(s) affected)