Search code examples
mysqlregexsql-like

MySQL update of monetary values to be 'consumer friendly'

I've got a field in a mysql database that contains prices for a store. As a result of some math calculations (tax etc) a lot of the numbers end in 'unfriendly' amounts, mostly .93 or .94, when the client wants all prices to end in .95

Can up do an update to force .95 to be the value after the decimal place for every record? Would that be a regexp, or can I do it with a LIKE?

Solution

  • TRUNCATE, ROUND, CEIL and FLOOR will do it.

    mysql> SELECT 
    TRUNCATE(1.93, 0)+0.95  AS `TRUNCATE`,
    ROUND( 1.93) -0.05  AS `ROUND`,
    CEIL(1.93) - 0.05 AS `CEIL`,
    FLOOR(1.93)+0.95 AS `FLOOR`;

+----------+-------+------+-------+
| TRUNCATE | ROUND | CEIL | FLOOR |
+----------+-------+------+-------+
|     1.95 |  1.95 | 1.95 |  1.95 |
+----------+-------+------+-------+