How Can I Change MySQL\'s Decimal Separator from a Dot to a Comma Without Using FORMAT?

Altering Decimal Separator in MySQL Output

The question arises as to whether it is feasible to modify MySQL's output decimal separator from "." (dot) to another character (comma) without the need for functions like FORMAT.

To achieve this, one can modify MySQL system variables controlling the number formatting. The following steps outline the process:

1. Connect to the MySQL database.
2. Execute the query "SHOW GLOBAL VARIABLES LIKE 'lc_time_names%';" to display the current localization settings.
3. Set the "lc_time_names" variable to the desired locale. For example, to use a comma as the decimal separator, set it to "en_US.UTF-8" or "en_GB.UTF-8."
4. Execute the query "SET GLOBAL lc_time_names='new_locale';" to apply the modified localization settings.

The changes take effect immediately, and all subsequent queries will use the specified decimal separator.

Note for CSV Exports:

For CSV exports, it is possible to utilize the "REPLACE" function. The query "SELECT REPLACE(CAST(prijs_incl AS CHAR), '.', ',')" converts a column named "prijs_incl" to a character string and replaces the decimal points with commas. This transformed output can then be used as numeric fields in European Excel spreadsheets.

The above is the detailed content of How Can I Change MySQL\'s Decimal Separator from a Dot to a Comma Without Using FORMAT?. For more information, please follow other related articles on the PHP Chinese website!