SQL Type Conversion Failure Still Exists Outside of Set

Question

Good afternoon folks.

I'll preface this with "this was a hard question to ask". I'm running into conversion errors when I think I've got that part covered. Obviously, I do not.

The situation: VARCHAR field with INT data in it, plus some random garbage strings that are causing conversion issues. Here's an example of what I'm trying to do.


    DECLARE @MyTABLE TABLE (
      Value VARCHAR(50) NOT NULL
    );

    -- insert some strings
    INSERT INTO @MyTABLE (Value) VALUES('400'), ('H-100'), ('H-200'), ('500'),
    ('600'), ('H-300');

    -- conversion fails for the actual strings
    SELECT *
    FROM @MyTABLE m
    WHERE CAST(m.Value AS INT) BETWEEN 1 AND 1000;

    -- what I THOUGHT would fix it, but doesn't...
    SELECT *
    FROM (SELECT * FROM @MyTABLE WHERE Value NOT LIKE 'H%') X
    WHERE CAST(X.Value AS INT) BETWEEN 1 AND 1000;

I realize there are other ways that I can do this, such as inserting all BUT the bad data into a temp table and querying that, but I'd like to know why my query doesn't work and what I could do to fix it.

EDIT - This is for SQL 2008 R2

Thanks in advance!

possible duplicate of [unable to cast value as float](http://stackoverflow.com/questions/7192524/unable-to-cast-value-as-float) — Martin Smith, Jun 18 '13 at 19:48
SQL Server can push the `CAST` down before the `WHERE` even with the derived table. You need to use a `CASE` expression (`CAST(CASE WHEN m.Value NOT LIKE '%[^0-9]%' THEN m.Value END AS INT)`) or `TRY_CONVERT` dependant on version. — Martin Smith, Jun 18 '13 at 19:49

Hart CO · Accepted Answer · 2013-06-18T20:18:34.300

1

It is an odd bit, if you notice it's the BETWEEN clause that kills it, because the WHERE isn't being evaluated when you think it should, you can use:

SELECT *
FROM @MyTABLE
WHERE CASE WHEN ISNUMERIC(Value)=1 THEN value ELSE 0 END
      BETWEEN 1 AND 1000

Demo: SQL Fiddle

edited Jun 18 '13 at 20:18

answered Jun 18 '13 at 19:52

Hart CO

34,064
6
48
63

This query doesn't seem to resolve the problem, but I see it's aimed for. – hughball Jun 18 '13 at 20:15
Added demo that works fine, what value is causing an error on your end? – Hart CO Jun 18 '13 at 20:19
@hughball `ISNUMERIC` doesn't guarantee it will cast to `INT` successfully (though it does for the sample data in the question). If your actual data has stuff like `$` then this test can still fail but fundamentally correct. Just needs a slightly more involved test. – Martin Smith Jun 18 '13 at 20:20
Just me trying to run it without using my brain. Works great, thanks! – hughball Jun 18 '13 at 20:20
@MartinSmith In the case of my actual data, this will suffice, but thanks for the heads up. – hughball Jun 18 '13 at 20:22

SQL Type Conversion Failure Still Exists Outside of Set

1 Answers1