I have a column of data type varchar, some of which contain numerical (decimal) values. I need to find all rows with numerical values > 100. I'm using ISNUMERIC to filter rows that contain numbers, after which I'm trying to use CAST or CONVERT to change the numbers to a decimal value. I read another suggestion that said using a sub-query would fix the problem but I've tried that as well with no luck
This is the error message I'm receiving:
Arithmetic overflow error converting varchar to data type numeric.
Code:
select field_name
from table_name
where ISNUMERIC(field_name) = 1 and
field_name in (select cast(field_name as decimal)
from table_name
where ISNUMERIC(field_name) = 1
and field_name not like '%,%'
and field_name not like '%-%'
and field_name != '.'
and field_name > 100.00)
This is your query:
select field_name
from table_name
where ISNUMERIC(field_name) = 1 and
field_name in (select cast(field_name as decimal)
from table_name
where ISNUMERIC(field_name) = 1 and
field_name not like '%,%' and
field_name not like '%-%' and
field_name <> '.' and
field_name > 100
)
You are making the assumption that the where clause is filtered before the select. This is not true. In fact, SQL Server pushes the cast() to the operation that reads the data. It then applies the filter afterwards.
In fact, subqueries and common table expressions also do not guarantee the order of evaluation. What does is case (at least in this case that involves no aggregation). So, you can write your query as:
select field_name
from table_name
where (case when ISNUMERIC(field_name) = 1 and
field_name not like '%,%' and
field_name not like '%-%' and
field_name <> '.'
then cast(field_name as decimal)
end) > 100;
I see no reason for a subquery.