1
I am developing an application that needs to automatically export excel sheets to the database. I’m using Mysql, but the spreadsheet data is not all well formatted the way the database accepts.
I’ve already set out as utf8_general_ci, all tables with utf8 and did not solve. The problem is when I read a number of type 1.478,45 and it can’t store in a variable like double or float.
How to proceed?
3
In your favorite programming language, do something like:
var numero = foo; // onde "foo" é o número quebrado;
numero = (numero + '').replace('.', '').replace(',', '.');
3
Guy like our friend said before, in mysql he uses the American currency standard that follows this pattern '15,123.50' .
Unlike the Brazilian standard that is '15.123,50' which is unlike how you saw.
however in mysql BD besides being American standard it does not count the houses of thousands thus getting '15123.50' .... so you should make a way to put this pattern in the language used by vc !!
In PHP I do it the following way friend :
valor = valor.replace(".", "") ; //IMPORTANTE - Tirar os pontos (a cada 3 dígitos, exemplo, 1.355.000,00)
valor = valor.replace(",", ".") ; // Depois remover a virgula da casa decimal por ponto, ficando no padrão d mysql
Browser other questions tagged mysql mysql-workbench
You are not signed in. Login or sign up in order to post.
Because the number does not follow this format. It only has decimal point division (it does not have the thousand separator). And normally the decimal separator is "
." and not "," unless configured with the appropriate locale– Jefferson Quesado
Nor should it be stored in this format. In DB the data should always be stored by its value, not by its "aesthetic". Storing number with thousand separator in DB is almost how to want to "store text in bold". DB does not have these visual concepts.
– Bacco