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.

Answer 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.