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

Question

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

Answer 1

You'd have to force SQL to evaluate the expressions in a certain order. Here is one solution

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

and another

Select *
From table
Where

CASE
   WHEN IsNumeric(myField) = 1 And IsNull(myField, '') <> ''
   THEN Convert(int, myField) ELSE @StartRange-1
END Between @StartRange And @EndRange

• The first technique is "intermediate materialisation": it forces a sort on a working table.
• The 2nd relies on CASE ORDER evaluation is guaranteed
• Neither is pretty or whizzy

SQL is declarative: you tell the optimiser what you want, not how to do it. The tricks above force things to be done in a certain order.

Answer 2

Not sure if this helps you, but I did read somewhere that incorrect conversion using CONVERT will always generate error in SQL. So I think it would be better to use CASE in where clause to avoid having CONVERT to run on all rows

Answer 3

Use a CASE statement.

declare @StartRange int
declare @EndRange int

set @StartRange = 1
set @EndRange = 3

select *
from TestData
WHERE Case WHEN ISNUMERIC(Value) = 0 THEN 0
            WHEN Value IS NULL THEN 0
            WHEN Value = '' THEN 0
            WHEN CONVERT(int, Value) BETWEEN @StartRange AND @EndRange THEN 1
            END = 1

Answer 4

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)