How to Format Integers as Currency in MySQL?

MySQL Technique: Select Integer as Currency Format

When dealing with monetary values in MySQL databases, the need often arises to convert integers or strings into currency format for presentation purposes. To address this requirement, two primary approaches are available.

1. Concatenation:

One option is to concatenate the currency symbol with the integer using the CONCAT() function. While this method is straightforward, it can be inefficient and prone to errors when dealing with large numeric values.

2. FORMAT() Function:

An alternative and more robust approach is to utilize the FORMAT() function. This built-in function allows for advanced string formatting, including currency conversion. To convert an integer to currency format, use the following syntax:

FORMAT(val, 2)

Where:

• val is the integer or string to be formatted.
• 2 specifies the number of decimal places to display.

The FORMAT() function automatically converts the integer to a string and applies the appropriate currency format. For example:

SELECT CONCAT('$', FORMAT(1000, 2)) AS currency;

Output:

 ,000.00

This approach provides a clean and reliable method for converting integers to currency format with customizable formatting options.

The above is the detailed content of How to Format Integers as Currency in MySQL?. For more information, please follow other related articles on the PHP Chinese website!