Introduction:

In this blog, we will explore multiple methods to achieve the Decimal to Currency Format in MySQL, from basic string manipulation to utilizing built-in functions. We will provide detailed explanations, code examples, and outputs for each method.

Method 1: Using CONCAT() and String Formatting

To begin our blog, let's start with a straightforward method that involves concatenating currency symbols with the formatted decimal value. This approach is ideal when you have a fixed currency symbol for your application.

SELECT CONCAT('$', FORMAT(decimal_column, 2)) AS formatted_currency FROM your_table;

In this query, the FORMAT() function is used to round the decimal value to two decimal places and add commas for thousands separation. Then, the CONCAT() function combines the currency symbol '$' with the formatted decimal value, creating a visually appealing currency representation.

Output:

Method 2: Using the FORMAT() Function with Locale

Sometimes, applications require displaying currency values based on the user's locale. The following method accomplishes this by utilizing the FORMAT() function with the LC_MONETARY locale setting.

SET lc_monetary = 'en_US';
SELECT FORMAT(decimal_column, 2) AS formatted_currency FROM your_table;

The SET lc_monetary = 'en_US'; statement sets the locale to US English, ensuring that the currency format adheres to the standard in that region. The FORMAT() function then formats the decimal value accordingly.

Output:

Method 3: Using CAST() and Numeric Formatting

For applications that demand more control over formatting, you can use the CAST() function in combination with numeric formatting.

SELECT CONCAT('$', CAST(decimal_column AS DECIMAL(10, 2))) AS formatted_currency FROM your_table;

In this method, the CAST() function is employed to explicitly convert the decimal value to the desired format with two decimal places. Then, the CONCAT() function adds the currency symbol '$' to the formatted value.

Output:

Conclusion:

In this blog, we have explored multiple methods to format decimal numbers as currency values in MySQL. Whether you need a simple concatenation approach, locale-based formatting, or precise control over numeric representation, these methods provide flexibility to cater to your specific application requirements. By utilizing techniques like FORMAT(), CONCAT(), and CAST(), you can ensure that your data is accurately presented in a readable and meaningful currency format.