CODEWITHSUNDEEP
mysql
user10828115
user10828115

Reputation:

How to store integer data with comma in it?

I am having some difficulties about storing integer data with commas, I have prices, which is like 4,600 So I need to store it with commas but when I try to send it as Integer it cut after first number. I tried to change column type. BigInt or Double but it doesn't effect any. Any possible way to do that?

Also tried to change comma to dot "." but with this, mysql delete the "0" at last... I don't know why... 

Prices
------
4,500
2,300
1,500

Upvotes: 0

Views: 10235

Answers (2)

t.niese
t.niese

Reputation: 40842

If you want to be able to do calculations using SQL queries with those numbers, then you need to store the price either use the DECIMAL type or use integer types and multiply the number by e.g. 1000 before saving.

Double or any other floating point representation of numbers are not suitable for price calculations/storage.

If you use DECIMAL need to convert the number form your local format 4,5000 to the format the database expects when you store it in the database, and convert it back to the local format when you retrieve your data.

If you store it as string then you can keep your local format but that's the worst solution, and should never be used.

Upvotes: 1

Agi Hammerthief
Agi Hammerthief

Reputation: 2134

Because you're using a comma, MySQL most likely interprets the number as two fields, separated by the comma. For example:

Prices,Unspecified
------,-----------
4     ,500
2     ,300
1     ,500

In the numbers in question: If the comma is a thousands separator, remove it (via String replace) before trying to store the number. If it's a decimal point, replace it with a period (via String replace) and store it as a DOUBLE (or DECIMAL if you need high accuracy for large numbers).

If you want to display the number with a comma, use String formatting (possibly a number-formatting function other than String.format() or sprintf()) after retrieving the value from the database.

Upvotes: 2

Related Questions