1

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.

Thanks

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

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 '18 at 0:23
0
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 '18 at 0:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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