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

Evyn

The derived table doesn't get materialised first then the WHERE clause applied. It is treated more like a view where the optimiser will just rewrite the 2nd one to be like the first as from a relational algebra POV they are the same. – Bust

According to [this page](msdn.microsoft.com/en-us/library/aa226054(SQL.80%29.aspx), there is no need to explicitly convert nvchar to int. – Subtorrid

Answer 1

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)

Stall

This in combination with the case statement works like a charm. You actually helped me resolve an arithmetic overflow converting a number that was too long for bigint even. So that was helpful. +1 – Evyn

@Evyn - excellent - so I answered your follow up question before you had a chance to post it? :-) – Stall

Answer 2

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.

Individuality

Actually, the question proves this SQL implementation is not declarative. You tell the optimizer what you want, and it fails to convert your declaration in a viable execution plan. In a true declarative language, even the condition Convert(int, myField) Between @StartRange And @EndRange AND IsNumeric(myField) = 1 is correct. (Easy with ternary logic, where NULL AND FALSE is FALSE) – Lateritious

@MSalters: I would say that the BETWEEN or the ISNUMERIC are contradictory at that level. SQL is doing its declarative thing and deciding how best to get the data, bot what ORDER to evaluate conditions. BETWEEN is more likely to use an index, ISNUMERIC isn't. Rewriting a rubbish query because of crap data is not part of the "declarative" definition – Individuality

Sorry, thought the "ternary logic" bit made it clear. The Convert side of the expression would evaluate to NULL (error) whenever the IsNumeric side evaluates to FALSE. A true declarative language wouldn't judge this a "rubbish query" as the result is very well-defined. – Lateritious

@MSalters: I disagree. FALSE AND NULL is FALSE too. "AND" requires all expressions to be evaluated: there is no short circuit AND in SQL Server. Relying on evaluation of the WHERE clause in a certain order is procedural, no? The fact that datatypes are wrong, design is wrong and tests contradictory does not invalidate "SQL is declarative". Unless you want SQL to allow for all manner of bad design permutations... just why is there BETWEEN on a string column anyway? – Individuality

I don't think you can rely on a short circuit OR in SQL server either. – Rana

Answer 3

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

Hadji

This works, I'm not sure it's optimal, but that's at least a step further than I was before. Thanks. – Evyn

Answer 4

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

Fateful