This question already has an answer here:
- CTE Error (nvarchar to numeric) 1 answer
My query looks a bit like this:
Select top (@n) * From MyTable Where IsNumeric(MyColumn) = 1 Order By Cast(MyColumn as int) desc
Occasionally Sql will produce a query plan that places the Sort operator ahead of the filter operator. This is problematic since the table can contain values which are non-numeric.
Is there a query hint I can apply to force the filter to always come first?
Or would it be better to write my order-by clause to safely handle non-numeric values? I've found two options for this, but I'm not sure which one is better:
Order By Cast(Stuff(MyColumn, 1, Patindex('%[0-9]%', 1), '') as int) desc
Order By Case IsNumeric(MyColumn) When 1 Then Cast(MyColumn as int) else 0 end desc