Using comma as decimal separator in database queries

Question

I've set Swedish locale to use comma as decimal separator. For example it should be 123,45 instead of 123.45.

To test it:

echo 100/3;

It gives desirable result:

33,333333333333

However when fetching or inserting data in the database using PHP PDO, it does not seem to work. For if I try to update a table row:

$sql = "UPDATE
            table_name
        SET
            column_name = 123,45 //does not work but 123.45 works.
        WHERE   
            row_id = 1
    ";  
$q = $db->prepare( $sql );
$q -> execute();

In the above code I get error if I try to use a comma separator eg. 123,45 but it works if I use dot. Similarly when I select the data

$sql = "SELECT
            column_name
        FROM
            table_name
        WHERE   
            row_id = 1
    ";  

    $q = $db->prepare( $sql );
    $q -> execute();
    $test = $q -> fetch( PDO::FETCH_COLUMN );
    var_dump($test); //string "123.45"

It will return the result with dot as a decimal separator. ie. 123.45 while I would like 123,45.

What I'm doing wrong? Edit: The column data type is numeric with scale 2.

Possible duplicate of [Change decimal separator in MySQL](https://stackoverflow.com/questions/8669212/change-decimal-separator-in-mysql) — Mike, Feb 08 '18 at 07:09

Miggy · Answer 1 · 2018-02-08T07:15:36.203

1

I think this is because the comma is mistakenly read by the SQL as the comma separator in the query statement.

So I think you could do something like this:

$sql = "UPDATE
        table_name
    SET
        column_name = '123,45'
    WHERE   
        row_id = 1
";

edited Feb 08 '18 at 07:15

answered Feb 08 '18 at 07:09

Miggy

816
7
16

Thanks for answering, the column data type is numeric, so it won't work in that case. – user966582 Feb 08 '18 at 07:12
sorry about that. You could try to change the data type to make it work because comma will not be stored using the current data type – Miggy Feb 08 '18 at 07:13
You also can't have backticks around strings – Mike Feb 08 '18 at 07:14

score 1 · Accepted Answer · answered Feb 08 '18 at 07:28

1

This would be impossible for MySQL to handle because the comma is also the separator character in a list. Take for example the following query:

INSERT INTO table (a,b) VALUES(123,45,67)

What does this mean? Are the values 123 and 45.67? Or 123.45 and 67? Could be either one. It would be totally ambiguous.

I would suggest doing all your business logic in PHP using en_US and changing the locale to Swedish only for output.

answered Feb 08 '18 at 07:28

Mike

23,542
14
76
87

Thanks, that makes sense. So I should convert 123,45 to 123.45 before inserting into database and then convert back 123.45 from database to 123,45 to display it? – user966582 Feb 08 '18 at 07:34
You *could* do it that way, but it would be rather error-prone, like if you forget to do one of the conversions. Instead just switch once before you start outputting the page content. Of course, if your code mixes logic with output this will be nearly impossible without refactoring. – Mike Feb 08 '18 at 07:38
1

What I mean is, use `en_US` (or similar) by default and switch to whatever other locale you want only for outputting content to the page. All calculations, manipulations and database queries should be done using a locale like `en_US`. – Mike Feb 08 '18 at 07:45
That's right but the user input will be in local format ie. 123,45, so I guess in that case it is necessary to convert it to `en_US` before insert in database or doing any calculation. – user966582 Feb 08 '18 at 07:49
All numbers in the database will be in the `en_US` decimal format, so there would be no need to convert that until it is outputted to the page. The only thing that you would first have to convert into `en_US` would be direct user input (i.e. `$_GET`, `$_POST`, `$_COOKIE`, `$_REQUEST`). – Mike Feb 08 '18 at 18:20

score 1 · Answer 3 · answered Feb 08 '18 at 07:32

You can use PHP number_format() function.

This function accepts either one, two, or four parameters (not three):

If only one parameter is given, number will be formatted without decimals, but with a comma (",") between every group of thousands.

If two parameters are given, number will be formatted with decimals decimals with a dot (".") in front, and a comma (",") between every group of thousands.

If all four parameters are given, number will be formatted with decimals decimals, dec_point instead of a dot (".") before the decimals and thousands_sep instead of a comma (",") between every group of thousands.

Example:

<?php

$number = 1234.56;

// english notation (default)
$english_format_number = number_format($number);
// 1,235

// French notation
$nombre_format_francais = number_format($number, 2, ',', ' ');
// 1 234,56

$number = 1234.5678;

// english notation without thousands separator
$english_format_number = number_format($number, 2, '.', '');
// 1234.57

?>

Source: http://php.net/manual/en/function.number-format.php

This would work if the **only** thing needed for the other locales are the number formats, however there are many other things that can be changed when setting the locale. See http://php.net/manual/en/function.setlocale.php — Mike, Feb 08 '18 at 07:35

Using comma as decimal separator in database queries

3 Answers3