How does one filter based on whether a field can be converted to a numeric?

Question

I've got a report that has been in use quite a while - in fact, the company's invoice system rests in a large part upon this report (Disclaimer: I didn't write it). The filtering is based upon whether a field of type VarChar(50) falls between two numeric values passed in by the user.

The problem is that the field the data is being filtered on now not only has simple non-numeric values such as '/A', 'TEST' and a slew of other non-numeric data, but also has numeric values that seem to be defying any type of numeric conversion I can think of.

The following (simplified) test query demonstrates the failure:

Declare  @StartSummary Int,
         @EndSummary Int

Select   @StartSummary = 166285,
         @EndSummary = 166289

Select   SummaryInvoice
From     Invoice
Where    IsNull(SummaryInvoice, '') <> ''
And      IsNumeric(SummaryInvoice) = 1
And      Convert(int, SummaryInvoice) Between @StartSummary And @EndSummary

I've also attempted conversions using bigint, real and float and all give me similar errors:

Msg 8115, Level 16, State 2, Line 7 Arithmetic overflow error converting expression to data type int.

I've tried other larger numeric datatypes such as BigInt with the same error. I've also tried using sub-queries to sidestep the conversion issue by only extracting fields that have numeric data and then converting those in the wrapper query, but then I get other errors which are all variations on a theme indicating that the value stored in the SummaryInvoice field can't be converted to the relevant data type.

Short of extracting only those records with numeric SummaryInvoice fields to a temporary table and then querying against the temporary table, is there any one-step solution that would solve this problem?

Edit: Here's the field data that I suspect is causing the problem:

SummaryInvoice

11111111111111111111111111

IsNumeric states that this field is numeric - which it is. But attempting to convert it to BigInt causes an arithmetic overflow. Any ideas? It doesn't appear to be an isolated incident, there seems to have been a number of records populated with data that causes this issue.

score 3 · Accepted Answer · answered Jan 26 '11 at 19:53

It seems that you are gonna have problems with the ISNUMERIC function, since it returns 1 if can be cast to any number type (including ., ,, e0, etc). If you have numbers longer than 2^63-1, you can use DECIMAL or NUMERIC. I'm not sure if you can use PATINDEX to perform an regex look on SummaryInvoice, but if you can, then you should try this:

SELECT SummaryInvoice
FROM Invoice
WHERE ISNULL(SummaryInvoice, '') <> ''
AND CASE WHEN PATINDEX('%[^0-9]%',SummaryInvoice) > 0 THEN CONVERT(DECIMAL(30,0), SummaryInvoice) ELSE -1 END
BETWEEN @StartSummary And @EndSummary

+1 I forgot about the %[^0-9]% trick. I reckon OP needs decimal(38,0) too after update — gbn, Jan 26 '11 at 20:21

score 1 · Answer 2 · edited May 23 '17 at 12:13

1

You can't guarantee what order the WHERE clause filters will be applied.

One ugly option to decouple inner and outer.

SELECT
   *
FROM
    (
    Select   TOP 2000000000
             SummaryInvoice
    From     Invoice
    Where    IsNull(SummaryInvoice, '') <> ''
    And      IsNumeric(SummaryInvoice) = 1
    ORDER BY SummaryInvoice
    ) foo
WHERE
    Convert(int, SummaryInvoice) Between @StartSummary And @EndSummary

Another using CASE

Select   SummaryInvoice
From     Invoice
Where    IsNull(SummaryInvoice, '') <> ''
    And     
    CASE WHEN IsNumeric(SummaryInvoice) = 1 THEN Convert(int, SummaryInvoice) ELSE -1 END
          Between @StartSummary And @EndSummary

YMMV

Edit: after question update

use decimal(38,0) not int
Change ISNUMERIC(SummaryInvoice) to ISNUMERIC(SummaryInvoice + '0e0')

edited May 23 '17 at 12:13

Community

1
1

answered Jan 26 '11 at 19:17

gbn

422,506
82
585
676

I already tried your first example which gave me `Arithmetic overflow error converting expression to data type bigint`. I thought I tried your second example but didn't get that to work, will check again. – BenAlabaster Jan 26 '11 at 19:21
@BenAlabaster Then you should try a `CONVERT(BIGINT,` instead of a `CONVERT(INT,` – Lamak Jan 26 '11 at 19:24
+1, but you should be cautious with the use of `ISNUMERIC`, you can have trouble converting to `INT` – Lamak Jan 26 '11 at 19:32
@Lamak if you'd noted my comment you'd have realized I already tried bigint. And again, IsNumeric will report that any value made up of digits (plus a number of other characters) is numeric, for instance, +, -, $ and any other currency signs are all numeric. The problem is, how do I take a numeric string containing 26 digits and use it as part of a between clause? – BenAlabaster Jan 26 '11 at 19:36

score 0 · Answer 3 · answered Jan 26 '11 at 19:21

0

AND with IsNumeric(SummaryInvoice) = 1, will not short circuit in SQL Server.

But may be you can use

AND (CASE IsNumeric(SummaryInvoice) = 1 THEN Convert(int, SummaryInvoice) ELSE 0 END) Between @StartSummary And @EndSummary

answered Jan 26 '11 at 19:21

Nitin Midha

2,258
20
22

score 0 · Answer 4 · answered Jan 26 '11 at 20:02

0

Your first issue is to fix your database structure so bad data cannot get into the field. You are putting a band-aid on a wound that needs stitches and wondering why it doesn't heal.

Database refactoring is not fun, but it needs to be done when there is a data integrity problem. I assume you aren't really invoicing someone for 11,111,111,111,111,111,111,111,111 or 'test'. So don't allow those values to ever get entered (if you can't change the structure to the correct data type, consider a trigger to prevent bad data from going in) and delete the ones you do have that are bad.

answered Jan 26 '11 at 20:02

HLGEM

94,695
15
113
186

This database is a legacy system that's in the process of being phased out as we speak. The field isn't an amount so the assumptions you specified aren't really relevant in this situation. Refactoring the database may be a solution, but there's a few million records in this table with a number of resources sitting on top of them making that task more than arduous. It's really key that I find a solution to the issue without more work than necessary in this instance, but that will allow staff requiring this reports to be tied over until the new product is released shortly. – BenAlabaster Jan 26 '11 at 21:16

How does one filter based on whether a field can be converted to a numeric?

4 Answers4