Querying Fields with different datatypes

[ryan_b83]

Hello, I have a question. I have a table in a database that stores all the "additional attributes" of products. Because not all products have the same "additional attributes" i have a table that just holds all the instances of them.

For Example:

adtraAttributeID = (int & primary key)
attributeName = (int forgien key refering to a list of all the additional attributes in another table)
attributeValue = the actual value of that attribute.

The problem is that the values are sometimes strings, sometimes numbers. Since i store them all as varchar's thats not really a problem until i want to query them. Is there a way to query a varchar but treat it as a number?

Thanks,
Ryan

[FalseDawn]

If the column is a varchar, you should query it as a varchar, regardless of whether it contains a "number" or not.

eg, if your column is varchar(50), say

You should do something like this
SELECT * FROM mytable WHERE mycolumn='123'

NOT
SELECT * FROM mytable WHERE mycolumn=123

Although it may be the case that the mySQL engine will implicitly convert to a varchar, I'd never assume that.

[ryan_b83]

Yes, i understand that... but what happens when i try to do a query

SELECT * FROM table WHERE attribute > '5.3'

Is there a function that can force it to treat the field as a number?

[FalseDawn]

No, if you need to do numeric comparisons, you need to use a numeric field.

[ryan_b83]

What about when you need a column to store 2 different datatypes?

Size can be. 1,2,3,4,5,s,m,l,xl

Some items sizes are number vaues, some items sizes are letter values?

[FalseDawn]

Your best bet is to try it and see.

From a quick test I did, it seems as though mySQL does implicitly evaluate such expressions, even in a varchar field, treating non-numerics as zero.
IMO though, it's sloppy database design, and I'd use 2 separate fields.