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.
The first thing I would do is identify rows where those 6 characters can't possibly be an integer.
SELECT ProcessInstanceAppianID, ProcessInstanceDescription
FROM InternalUseOnly.dbo.processinstance
WHERE RIGHT(ProcessInstanceDescription, 6)
NOT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]';
Then fix that data or somehow exclude it from the results. However this gets tricky because you have no control over whether SQL Server will try the conversions before or after the filter (even if the filter is in a CTE or subquery). So you could dump the filtered results to a #temp table, for example, and then run the conversions on the output.
In SQL Server 2012+ you can use TRY_CONVERT() instead.
Best Answer
The good news: You're not missing anything obvious or arcane.
The bad news: If you've got a proper DBA onsite, it's time to get them involved.
Dollars to donuts the error is in the
object_definition()ofdbo.vw_AUTH_CREFNO. Typically thevw_prefix indicates that the object is aVIEW, meaning that it's just a wrapper for a largerSELECTstatement which itself can have a series of poorly writtenJOINs, malformed expressions, and nested evaluations.If you have
VIEW DEFINITIONpermission in theGIS_Testdatabase on serverzzzz(and the object is not encrypted), it's time to try extracting the base definition and crossing your fingers the DDL is sensibly written and it's an obvious error. In that Object Explorer pane you've got open, right-clickdbo.vw_AUTH_CREFNOand hit Script View as Create To New Query Editor Window, then Ctrl+f and hope like hell you findNCPRas a string literal in the code. What's more likely though is that you've got one ore more of the followingJOINing to a numeric column somewhere+-/*=characters)If you're lucky enough that
NCPRis a string literal somewhere in code, but it's nested below the first level object AND it's still in the sameGIS_Testdatabase, you can spelunk pretty broadly for it using the following snippet.If you're not so lucky, you need to look for that value in the base data, or try more advanced methods looking for which (of possibly several) problematic expression is causing your current problem.
Good luck! ¯\_(ツ)_/¯