I am trying to update selected data from a table column and format this data as currency.

My table column holds strings that have both mixed whole numbers and already formatted data. This was due to the fact that I had migrated old data which were whole numbers but any new data or updated old data gets formatted correctly in the application and database.

I would like to select all the non-formatted data from this table column and then Update this data to currency format. 0 to 0.00, 200 to 200.00, 23702 to 23,702.00, 34000.00 to 34,000.00, 2954795.54 to 2,954,795.54

Any assistance would be highly appreciated.


  • Format as currency preserve string datatype or change field datatype to currency?
    – Akina
    Aug 21, 2018 at 17:55
  • Format non-formatted data as currency preserving datatype as string.
    – Brian N
    Aug 21, 2018 at 17:57
  • Simple update query. With WHERE NOT INSTR(field,',').
    – Akina
    Aug 21, 2018 at 18:19

2 Answers 2


You really want to store data in fields of the correct Data Type. If these values are monetary amounts, then hold them in a suitable Data Type, like Currency.

Any time you convert data from one type to another, you're make extra work for the database engine, having to do the conversion, and running the risk of losing the benefit on any Indexes you might have defined, because calling a function on a field in every row generally forces the database to Table Scan that table (unless you have function-based Indexes, but that's another story).

Using the "wrong" Data Type also means that some functionality "doesn't work" properly. If you're storing Numerical values as Strings, then comparing these values "goes all wrong".

For example, in the Numeric World ...

10 > 2  =>  TRUE

... as you'd expect, but in the Character world ...

'10' > '2'  =>  FALSE
  • For MySQL, "currency" is best held in something like DECIMAL(11,2).
    – Rick James
    Aug 30, 2018 at 0:23
UPDATE table
SET field = FORMAT(CAST(field AS DECIMAL(10, 2)), 2)
WHERE NOT INSTR(field,',')
  • I think that you don't need to call CAST; just use FORMAT(field, 2)
    – Rick James
    Aug 30, 2018 at 0:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.