Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

  1. The nature of this query is OK with not getting the rows that have letters.
  2. 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)
  3. 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?

Thanks for your time!

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
1  
This is precisely what I needed--to me, the order-of operations with subqueries seemed clearcut, but the realization that the optimizer takes liberties with those will help me avoid this in the future--thanks again. –  hexparrot Nov 2 '12 at 14:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.