7
I have a table in MYSQL (can be seen here http://sqlfiddle.com/#! 9/187ce/1 ), where the price is with commas and not points:
2.00 2,33 11.00
When I’m going to give an order by:
ORDER BY preco ASC
The system understands that it is a string and puts the 11.00 in front, how to make this conversion to number at the time of ORDER BY?
Just to tell you, I can’t modify the table, it’s not mine and I don’t have that permission.
6
If the price is commas, the field is probably not numerical. Ideally you would change the data type of the price field.
But if you need it fast, try
select cast(REPLACE(preco, ',', '.') as float ) p from tabela order by p
or try to update
update tabela set preco = REPLACE(preco, ',', '.');
then change the data type of the price column to float.
Ok for the value specified in the question, but if it has a value 1,00.39
In this case it is already another numeric format, so it is :update table set price = REPLACE(REPLACE(price, ' ', ''),'.',',');
5
In your specific case the transition to the type should be made decimal(18,2), but, there is an option that may cause a bit of slowness in the consultation, but, it works in a general way:
SELECT * FROM TABELA
ORDER BY CAST(REPLACE(REPLACE(preco, '.', ''),',','.') AS DECIMAL(18,2))
Cool, thanks! What would that 18.2?
18 numbers being 16 before the comma with two decimal places, is a basic formatting, which can be configured in its own way. @caiocafardo
3
As already answered, the only real solution is to correct the table field.
Anyway, if it is to keep the "compatibility mode", if the decimals are fixed, this is enough to simplify the expression:
SELECT * FROM produto ORDER BY LPAD(preco,10,'0')
See working on your own SQL Fiddle.
Exchange the 10 for as many houses as possible in the field. Basically we’re filling it with zeros on the left. The 10 is the amount of final boxes, and the 0 the character of alignment.
This works, because we organize the strings alphabetically like this:
0000002,00
0000004,10
0000011,50
Hi, think it is more effective from a performance point of view than AS DECIMAL and CAST?
@Caiocafardo I am suspicious to say, because I already have other answers, but the good old test is your friend :) What I can say is that, if the decimals are fixed, I would do it this way. The important thing is that you understand the risks and advantages of each method and apply it to your case. Understanding how each solution works is the most important thing. I like the simplicity of this one, its vulnerability is the case of numbers with decimals in different quantities.
I did tests and yours was more effective (much more), but really do not know how many decimal places can come, so far came only 2, but as the database is not mine... I can not beat the hammer!
@caiocafardo just see the size of the field in DB. Nothing prevents from putting houses left. Technically mine makes much less internal operations (no explanation would fit in the comment). See the size of the price field, and put in place of the 10. I think even if I used 50 houses, it would still be effective (internally it would be a Fill followed by strcmp, both efficient operations). On the possibility of varying the decimals, if the provider of the bank cannot give you certainty, do not risk.
0
I don’t have your table scheme here but try to do so:
order by cast (preco as real)asc
This will not give conversion error precisely because of the commas?
really not tested with comma, only caught a varchar field.
0
Try the following:
order by cast(replace(valor, '.', ',') as decimal(10,2));
Browser other questions tagged mysql sql
You are not signed in. Login or sign up in order to post.
this value could be a decimal?
– novic
@Virgilionovic yes, I need to turn to decimal 1.00 to 1.00, 2.33 to 2.33 (basically replacing the virgukla by dot would already make it interpret as number, no?
– caiocafardo
Hello, put your table create here.
– Cléverton Heming
If the value is numerical, the solution is to fix the DB. Very rare a situation where the data must be stored in the display format (in your case, obviously not appropriate). It is usually a basic architecture failure to store dates and numbers as a string, which generates a whole series of disadvantages.
– Bacco
Two answers appeared that work, but it is worth leaving a warning: the ideal is to have a column of the right type, preferably with index, and sort by it. Conversions and Places on the fly will certainly affect query performance (which may or may not be a problem in your case).
– bfavaretto
But man, if you’re gonna use this field for calculations and values, I think you better switch to the field type. Avoid these problems of conversions and inconvenience
– GabrielLocalhost
@Gabriellocalhost would be the ideal world, but I don’t have that permission, only if I take the entire customer table and copy it to a new table, but what if he inserts new products? I’d have to keep updating this with a cronjob... but I wasn’t hired to do it...
– caiocafardo