Casting text to a number

[fawkes]

I have a database that I can't modify and can't add stored procedures to.

In one of the tables there is some data with two fields I want to filter on:

Field1 holds the name of an attribute that and Field2 holds it's value.

Some attributes are numerical and some aren't and so Field2 is set up as varchar.

I can filter using

Field1='Attribute1' and case when isnumeric(Field2) then cast(Field2, Numeric) else Null end > 50

but when I add a second condition I get an error

(Field1='Attribute1' and case when isnumeric(Field2) then 
cast(Field2, Numeric) else Null end > 50)
or
(Field1='Attribute2' and Field2='MyText')

I get an error casting.

I've tried changing the number type and including a filter in the case to avoid null and empty string values in which case i get an arithmetic overflow.

Does anyone have any ideas how I can get this to work?

 

[SQLSister]

This one reason of many rreasons why that is a very poor database design. So sorry you can't change it. The problem is isnumeric includes things that can't be cast to a specific number type.

This FAQ might help:

http://www.tek-tips.com/faqs.cfm?fid=6423

"NOTHING is more important in a database than integrity." ESquared