How to store European Currency in MySQL?

Question

On a new project I work on I have data in CSV format to import into a mysql table. One of the columns is a price field which stores currency in the european format ie. 345,83. The isssue I have is storing this decimal seperator. In most European currencies the decimal seperator is "," but when I try to insert a decimal number into a field (ex. 345,83), I get the following error: "Data truncated for column 'column_name' at row 'row #'". If I use '.' instead of ',' it works fine. Could you please help me with, how to store this format in mysql?

Answer 1

You could import the currency field into a VARCHAR column and then copy this column into a DECIMAL column while replacing the , by a . in all rows using MySQL string-manipulation-functions.

UPDATE <<table>>
    SET <<decimal-currency-col>> = REPLACE(<<varchar-currency-col>>, ',', '.');

Answer 2

Try replacing the "," with "."?

$price = str_replace(",", ".", $price);

Answer 3

Some data types do not have a direct correlation between SQL Server or Access and MySQL. One example would be the CURRENCY data type: MySQL does not (yet) have a CURRENCY data type, but creating a column with the definition DECIMAL(19,4) serves the same purpose. While MSSQL defaults to Unicode character types such as nCHAR and nVARCHAR, MySQL does not so tightly bind character sets to field types, instead allowing for one set of character types which can be bound to any number of character sets, including Unicode.

from http://dev.mysql.com/tech-resources/articles/migrating-from-microsoft.html

Answer 4

Use number_format or money_format, it's pretty much what you preffer.

Answer 5

you can store it as a regular decimal field in the database, and format the number european style when you display it

edit: just added an example of how it might be achieved

$european_numbers = array('123.345,78', '123 456,78', ',78');

foreach($european_numbers as $number) {

    echo "$number was converted to ".convert_european_to_decimal($number)."\n";
    // save in database now
}

function convert_european_to_decimal($number) {
    // i am sure there are better was of doing this, but this is nice and simple example
    $number = str_replace('.', '', $number); // remove fullstop
    $number = str_replace(' ', '', $number); // remove spaces
    $number = str_replace(',', '.', $number); // change comma to fullstop

    return $number;
}

Answer 6

You could also consider multiplying it by 100 and storing it as INT.

Before inserting the price to the DB:

$price = (int)$price*100;

After receiving price from the DB:

$price = number_format($price, 2, ',', ' ');

Answer 7

It's worse than you think. The number 1234.56 may be written in Europe as:

1. 1234,56
2. 1 234,56 (space as a group separator)
3. 1.234,56 (dot as a group separator)

In .net the number parser can works according to a given culture, so if you know the format it does the hard work for you. I'm sure you can find a PHP equivalent, it'd save you a lot of trouble.