How to conditionally format decimal numbers in mysql SQL?

Hi Experts,

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:
#/0.00|526.84/#.


I know I can do concatenation like this:
  concat('#/', decAmt1, '|', decAmt2, '/#'), but is it possible to do a conditional format as described above?

Thanks much.

LVL 1
IT79637Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
Yes, you can use a CASE statement or IF.

SELECT CONCAT('#/', IF(decAmt1=0, 'true', 'false'), '|', CASE decAmt2 WHEN 0 THEN 'true' ELSE 'false' END, '/#')

However, for your case you may be better off just using FORMAT().  If you don't want the number to include the commas, you can just replace those.

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_format

SELECT CONCAT('#/', FORMAT(decAmt1, 2), '|', FORMAT(decAmt2, 2), '/#')

To replace the ',', just add a replace as mentioned:

REPLACE(FORMAT(decAmt1, 2), ',', '')
0
 
imgriffCommented:
Unfortunately MySQL does not support conditional(IF/ELSE) statements unless you use stored procedures. You night have to write your logic in PHP
0
 
gkhngkdmrCommented:
this sample may help you to format the number...

select '#'||to_char(00000.00,'FM0.90')||'|'||to_char(000000526.84,'FM99999999999990.00')||'#'  
from dual
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.