Question 21698 in Mysql Question posted in
The official MySQL documentation can be found here.

Converting a value to Currency – Mysql

this seems like a simple fix but I’m stuck.
I have a list of values ex: 8564899 that should be displayed as 85,648.99 and NOT 8,564,899.
If I can’t convert to currency. I would just like to cut the last two values off.
Any assistance is appreciated. Thank you!

I’ve tried the following solutions:

FORMAT(Value, 'F')
ROUND(Value, 2)
STR(Value, 7, 2)
CONVERT(DECIMAL(7,2), Value)
Tags:

3

Answers


  1. 0 
    SELECT concat(substring(Value, 1, length(Value)-2), ".", substring(Value, -2, 2)) as numWithDecimal

    Maybe like this to cut the value

    Login or Signup to reply.
  2. 0

    Convert to string

    CONVERT(Value,char)

    And insert the ‘.’ on the position in the string that you want

    Login or Signup to reply.
  3. 0

    I would suggest:

    SELECT FORMAT(Value/100, 2) AS Amount

    This should also work if Value is stored as a string/text/(var)char.

    If you need specific locale formatting look at the docs for FORMAT.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search