CODEWITHSUNDEEP
mysqldecimal
coder101
coder101

Reputation: 1605

MYSQL CAST AS DECIMAL for VARCHAR field

I have been given an e-commerce project to fix some errors on by my client. The earlier dumb developers had given the price field VARCHAR datatype instead of so very obvious INTEGER in the products table, and so the next set of developers in order for the search according to price range to work used CAST(PRICE AS DECIMAL). Now, i am the third one to work upon this and have found that it this CAST AS DECIMAL thing isn't strangely working only for the price range 0-500 but works for all otherr like 500-1000, 1000-2000 and so on.

The query is like:

The query so far is :

 SELECT * FROM tbl_product where status=1 and subcat_id='128' and
 price>'0' and price<='500' ORDER BY CAST(price AS DECIMAL(12,2)) ASC

This is somehow searching products with the price of 1000 and upwards.. Please help..

Upvotes: 3

Views: 8737

Answers (1)

Simon at The Access Group
Simon at The Access Group

Reputation: 7025

The best option here is to accept that VARCHAR is not the correct data type. Your plan of action is simple.

  1. Add new column priceNew DECIMAL(12,2)
  2. Migrate data from price into priceNew
  3. Once you've confirmed data is correct in priceNew, delete price and rename priceNew to price
  4. Remove all the CAST(price AS DECIMAL) hackjobs and start dealing with the number as a number
  5. Update anything that SELECT/UPDATE/INSERT's to price to make sure it deals with the correct new data type

This will give you far less headaches in the long run and will be more reliable / optimal. It will also allow MySQL to properly treat it as numeric instead of a string and so keys / values that select ranges based on that column will become more efficient.

Upvotes: 3

Related Questions