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
STUFFto no avail, however)
- I've tried TRY/CATCH but it does not appear to work when within a
WHEREstatement -- 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?