How Can I Change the Decimal Separator in MySQL Output?

Changing Decimal Separator in MySQL

By default, MySQL uses a period (".") as the decimal separator in output. However, there may be scenarios where you prefer to use a different character, such as a comma, to delimit decimal values.

While the FORMAT function can be used to temporarily change the decimal separator for specific queries, it requires modifications to existing queries. For a more global solution that doesn't involve altering queries, you can explore the following settings:

Using the DECIMAL_SEPARATOR System Variable:

MySQL's DECIMAL_SEPARATOR system variable allows you to specify the character used as the decimal separator in output. To change it to a comma, execute the following query:

SET DECIMAL_SEPARATOR = ',';

Configuring Locale Settings:

Depending on your operating system and MySQL version, changing the system locale settings can also affect the decimal separator. For example, in Linux, you can modify the /etc/locale.conf file and set the LC_NUMERIC locale to a region that uses a comma as the decimal separator.

Using CSV Exports:

For CSV exports specifically, you can use the REPLACE function to convert decimal points to commas. The following query demonstrates this approach:

SELECT REPLACE(CAST(prijs_incl AS CHAR), '.', ',')

Once any of these settings are configured, the decimal separator in MySQL output will change to your desired character. Note that these changes may affect all output from MySQL, not just specific queries.

The above is the detailed content of How Can I Change the Decimal Separator in MySQL Output?. For more information, please follow other related articles on the PHP Chinese website!