How can I format integers as currency in MySQL?

Currency Formatting in MySQL: Selecting Integers as Currency

MySQL provides several options to convert integer values to currency format during data retrieval. Here are the most common approaches:

1. CONCAT() with FORMAT()

The FORMAT() function allows you to apply formatting patterns to numbers. For currency formatting, you can use the following pattern:

FORMAT(number, decimal_places)

To convert an integer to currency format, you can use the following query:

SELECT CONCAT('$', FORMAT(val, 2)) AS formatted_currency ... ;

This will return the integer val formatted as a currency value with two decimal places.

2. String Replacement

Another option is to use string replacement to insert commas into a number string. This approach involves creating a custom function or using a built-in function such as SUBSTRING_INDEX():

CREATE FUNCTION format_currency(number INT)
RETURNS VARCHAR(255)
BEGIN
  DECLARE formatted_number VARCHAR(255);
  SET formatted_number = REPEAT('0', FLOOR(LOG10(number)) + 1);
  WHILE number > 0 DO
    SET formatted_number = SUBSTRING_INDEX(formatted_number, '0', -3);
    SET formatted_number = CONCAT(formatted_number, ',');
    SET formatted_number = CONCAT(formatted_number, SUBSTRING(CAST(number AS CHAR), -2));
    SET number = number / 100;
  END WHILE;
  SET formatted_number = SUBSTRING(formatted_number, 1, LENGTH(formatted_number) - 1);
  SET formatted_number = CONCAT('$', formatted_number);
  RETURN formatted_number;
END;

You can then use the format_currency() function to format your integer values as currency:

SELECT format_currency(val) AS formatted_currency ... ;

The above is the detailed content of How can I format integers as currency in MySQL?. For more information, please follow other related articles on the PHP Chinese website!