I have a varchar field in my table and I want to sort it. But I need to handle this field as integer. Meaning if sort as text the order is "19,2,20" but I want to get the right order "2,19,20".
Can anyone help me?
'LPAD(lower(column_name))' is used to sort the varchar field numerically in MySQL.
VARCHAR (length) The VARCHAR data type accepts character strings, including Unicode, of a variable length is up to the maximum length specified in the data type declaration. A VARCHAR declaration must include a positive integer in parentheses to define the maximum allowable character string length.
I somehow didn't manage to run the query with CAST
. I was always getting Error Code: 1064 near "DECIMAL"
(or other numeric type that I chose). So, I found another way to sort varchar
as numbers:
SELECT * FROM mytable ORDER BY ABS(mycol)
A bit simpler and works in my case.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With