Alright, I think I already know the answer to this question, but it's killing me anyway.

I have a column in my table (we'll call it priceCol for now) that is set to varchar(50) that is set to hold the prices of used cars, so it can store it either as $xx,xxx.xx or as xx,xxx.xx. Now when I want to do a "SELECT * FROM xxx WHERE priceCol > 0 AND priceCol < 500" now WHYYYYYYYY does this select return only some values? It returns values such as 50,000, 125,000, 200,000??????????? What am I doing wrong, granted I was working on this at like 3 in the am, so I prolly missed something or am doing something totally wrong that I know I shouldn't be.

Originally posted by DjPaj
Alright, I think I already know the answer to this question, but it's killing me anyway.

I have a column in my table (we'll call it priceCol for now) that is set to varchar(50) that is set to hold the prices of used cars, so it can store it either as $xx,xxx.xx or as xx,xxx.xx. Now when I want to do a "SELECT * FROM xxx WHERE priceCol > 0 AND priceCol < 500" now WHYYYYYYYY does this select return only some values? It returns values such as 50,000, 125,000, 200,000??????????? What am I doing wrong, granted I was working on this at like 3 in the am, so I prolly missed something or am doing something totally wrong that I know I shouldn't be.

It's not a good idea to use a varchar column to store numbers. Instead use for example a INTEGER column, and then in your frontend put in the commas and dollar-signs to make them readable for humans.

The reason your select does not work like you intended is, that the > and < compares according to normal alphabetical sorting (i.e. text sort), and not numerical.

Excellent that's what I thought thanks jks. I know the usual is to store numbers as int's, but this database was not made be me nor was the code, I am editing a prewritten script that a customer of mine wants to use. Thank you for your help though.

Is it possible to copy the contents of PriceCol into a numeric field within the script?

If it is, you could do your If >0 AND < 500 on that field.

Just a thought.