How can I query “between” numeric data on a non-numeric field?

Question

I have a query that I just found in a database that does not report a crash. The main essence of the request:

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

Now myField does not contain numeric data in all rows [this is an nvarchar type] ... but this query was explicitly designed in such a way that it only cares about rows where the data in this field is numeric.

The problem is that T-SQL (as I understand it) does not close the Where clause, causing it to break 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.

With the exception of dumping all rows where myField is numeric to a temporary table and then asking that for rows where the field is in the specified range, what can I do is optimal?

My first parsing is simply trying to parse the returned data and see what happens:

 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 as in the first request, which I am not sure I understand, since I am not converting data that should not be numeric at the moment. A subquery should only return rows in which myField contains numeric data.

Maybe I need my morning tea, but for whom does it make sense? Another set of eyes will help.

Thanks in advance

Answer 1

IsNumeric tells you that a string can be converted to one of the number types in SQL Server. It can convert it to money or to a float, but it cannot convert it to int.

Change

 IsNumeric(myField) = 1 

:

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

(i.e. you want myField to contain only numbers and fit into int)

Change examples:

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

result:

 ----------- ----------- ----------- 1 1 1 (1 row(s) affected) 

Answer 2

You need to force SQL to evaluate the expressions in a specific 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 further

 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 method is "intermediate materialization": it forces sorting on the desktop.
• 2nd relies on CASE ORDER score.
• Neither one nor the other happens.

SQL is declarative: you tell the optimizer what you want, not how to do it. The tricks above are to do things in a specific order.

Answer 3

Not sure if this will help you, but I read somewhere that incorrect conversion using CONVERT always causes an error in SQL. Therefore, I think it would be better to use the CASE clause in where to avoid using CONVERT for all rows

Answer 4

Use the 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