Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2002
    Southwest Michigan

    Treating an variable character as a string in MySQL

    I've got a database filled with rows that contain a field of type varchar that is technically a number that has already been formated, ie 256,039.39

    I would like to know how to treat this field as an integer for the purpose of doing a query such as

    WHERE `price` > 256,039.38

    to return the 256,039.39

    As well, I need to LIST the results in ascending integer order, not ASCII. Any clues?

    I cannot convert the formatted string to a straight integer so please do not suggest this. Thanks!!!

  2. #2
    Join Date
    Feb 2005
    Do you like to make things difficult for yourself?

    MySQL will treat strings as floating point numbers for the purposes of comparison, provided you take out the commas:
    SELECT price, REPLACE(price, ',', '') AS real_price
    FROM table
    WHERE REPLACE(price, ',', '') > REPLACE('256,039.38', ',', '')
    ORDER BY real_price;
    Of course it would be better and safer to convert the formatted string to a straight integer... but I guess you already know that

    "Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them." - Laurence J. Peter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts