SQL conversion failed when converting

0
votes

following situation:

a column xy is defined as varchar(25). In a view (SQL Server Mgmt Studio 2008) I filtered all values with letters (-> is not like '%[A-Z]%') and converted it to int (cast(xy as int)).

If I now try to make comprisons with that column (e.g. where xy < 1000), I'm getting a conversion error. And the message contains a value that should have been filtered with "is not like '%[A-Z]%'". Whats wrong??

thanks for help in advance...

this works (it folters out for example value 'G8111'): SELECT unid FROM CD_UNITS AS a INNER JOIN DEF_STATION AS b ON a.STATION = b.STATION WHERE (b.CURENT = 'T') and UNID like '%[A-Z]%'

but when i put that in a view, an make select on it:

select * from my_view where xy < 3000

system says 'Conversion failed when converting the varchar value 'G8111' to data type int.' but 'G8111' should be filtered out in query above...

1
sqlsql-serversql-server-2008view
Your filter is only for letters; are there other characters in your data (like commas or other punctuation) that won't cast to int? Plus, your filter is upper-case; is the value that should have been filtered lower-case? - Mike P
it would be more readable, you if would edit your question with the query for view generation itself - Olesya Razuvayevskaya
You might want to read and vote on SQL Server should not produce illogical errors - Damien_The_Unbeliever
sorry - deleted prev comment, wasn't thinking. the better filter would be WHERE ISNUMERIC(value) = 1 Then you guarantee its numeric at least (although could be outside the range of an int still) - James S
ISNUMERIC(value)=1 is a pointless check because, as you say, values that cannot be converted to int will be accepted, including such gems as £ by itself and 0d0. - Damien_The_Unbeliever

1 Answers

0
votes

The optimizer does crazy things at times, so despite the fact that an "inner" filter1 "should" protect you, the optimizer may still push the conversion lower down than the filter and cause such errors.

The only semi-documented place where it will not do this is within a CASE expression:

The CASE statement(sic) evaluates its conditions sequentially and stops with the first condition whose condition is satisfied. In some situations, an expression is evaluated before a CASE statement receives the results of the expression as its input.

...

You should only depend on order of evaluation of the WHEN conditions for scalar expressions (including non-correlated sub-queries that return scalars), not for aggregate expressions

So the only way that should currently work would be:

CASE WHEN xy NOT LIKE '%[^0-9]%' THEN CONVERT(int,xy) END < 1000

This also uses a double-negative with LIKE to ensure that it only attempts the conversion when the value only contains digits.

1Whether this be in a subquery, a CTE, a View, or even just considering the logical processing order of SELECT and WHERE clauses. Within a single query, the optimizer can and will push conversion operations past filters.