How to conditionally format decimal numbers in mysql SQL?
Posted on 2009-06-29
I have several decimal fields containing dollar amounts in table1. I am inserting decimal data from the first table into a second table using SQL "INSERT INTO table2 varchar_field SELECT .... FROM table1" sql statement. The target field in second table is varchar(125).
As part of the SQL, I want to perform a format and concatenation operation on the decimal data as follows:
First the Format operation:
000000000.00 --> 0.00 (leave one leading zero when = 0)
000000526.84 --> 526.84 (remove all leading zeros when not 0)
Second the Concatenate operation:
I know I can do concatenation like this:
concat('#/', decAmt1, '|', decAmt2, '/#'), but is it possible to do a conditional format as described above?