Getting "Conversion failed when converting the nvarchar value to data type int."

I have a SQL Server table pairing zipcodes and lat/long geometry, such as below

ID  ZIP5    geom
1   356HH   0xE610000001044E00....FFFFFFFF0000000003ID
2   35677   0xE6100000010404000000068....000003

The problem I'm having is that I cannot seem to convert from one type to the other due to the ZIP5 field having characters.

What I have tried is an approach wherein I pull only records that are fully integer-friendly:

SELECT
    zip,
    coordinates
FROM (
    SELECT
        CONVERT(int, [ZIP5]) AS zip,
        CONVERT(varchar(max), geom) AS coordinates
    FROM 
        [SpatialData].[dbo].[zip5]
    WHERE
        ISNUMERIC([ZIP5]) = 1
) AS t1
WHERE
    zip >= 85000 AND
    zip < 86000

I assumed that since the subquery returned an INT type, that there would be absolutely no problem doing normal comparison operators in the outer WHERE. I get the following error when executing this query:


Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the nvarchar value '356HH' to data type int.

Omitting the outer WHERE, I get a full result set with 100% integers in the ZIP5 column. Using the outer WHERE, it seems to look past the previous CONVERT and fail on conversion.

As a few extra notes:

• The nature of this query is OK with not getting the rows that have letters.

• It would be perfectly OK as well for the HH (or any other double char pair) to be replaced with numerical zeroes (tried REPLACE and STUFF to no avail, however)

• I've tried TRY/CATCH but it does not appear to work when within a WHERE statement -- when more broadly catching around the entire query, the try/catch will end up returning an empty set.

What approach would be the best to address without making a temp table--table variable?

Write your query this way. It includes a better test for integers and dumps ISNUMERIC that returns 1 for '-.', for example.

SELECT
    zip,
    coordinates
FROM (
    SELECT
        CASE WHEN ZIP5>'' AND NOT ZIP5 LIKE '%[^0-9]%' THEN
            CONVERT(int, [ZIP5]) END zip,
        CONVERT(varchar(max), geom) AS coordinates
    FROM 
        [SpatialData].[dbo].[zip5]
    WHERE
        ZIP5>'' AND NOT ZIP5 LIKE '%[^0-9]%'
) AS t1
WHERE
    zip >= 85000 AND
    zip < 86000

See this Connect item

SQL Server is free to evaluate the WHERE/SELECT clause in the order that it decides is optimized. A view or derived table is not materialized can easily be expanded into, from the outer query.

What SQL Server is compiling is really a query that looks like this

SELECT
    CONVERT(int, [ZIP5]) AS zip,
    CONVERT(varchar(max), geom) AS coordinates
FROM 
    [SpatialData].[dbo].[zip5]
WHERE
    ISNUMERIC([ZIP5]) = 1
    AND CONVERT(int, [ZIP5]) >= 85000
    AND CONVERT(int, [ZIP5]) < 86000

You can inspect the query plan of your original, but my guess from looking at the structure is that the WHERE clause uses the expression CONVERT(int, [ZIP5]) twice, so it makes sense to streamline the resolution (calculation to a result) of the expression in the process of retrieving data from the table. This puts the processing of the SELECT clause before the WHERE, so your ISNUMERIC() = 1 never got a chance to filter the bad eggs.

It is by-design.

StackExchange Database Administrators Q#28040, answer score: 2