Here's a quick tip at sorting VARCHAR type data in mysql database with values in a column.
With the default sort, it would look something like below:
mysql> SELECT column FROM table_name ORDER BY column; column ====== 100 1000 10000200 2000 20000 ...
Now with "... ORDER BY column+0", I get it sorted right:
mysql> SELECT column FROM table_name ORDER BY column+0; column ====== 100 200 1000 2000 10000 20000 ...
This is a quick fix instead of sorting to CAST operator.
Bookmark/Search this post with
- sandip's blog
- Login or register to post comments
Comments
Hi,just went I really needed. Thank you so much.
I have a varchar column with mixed data like 100/G/2014/PTUN-JAKARTA, 21/H/2014/PTUN-JAKARTA which need to be desc-ordered numerically. And your trick works like charm.
Great!
Thanks a lot dude.. Brilliant idea, works like a charm. Big hug from Northen part of India.. :)
dude - that worked PERFECTLY!! :D
If you ever visit Seriba (Europe) - I'll buy you a beer!! ^_^
THANKS BRO!! :D
dootzky
Bang! Nice job ... awesome.
This is the most awesome hack i've ever seen ... was already getting crazy sorting my varchar fields:
1200 <-- !! *args*
560
580
640
.
.
now it works! thanks you sooo much ;)
It worked a treat in my mysql script - so easy when you know how - or someone tells you!
@ULTIMATE
your solution worked for me in SQL
thanks a lot
It works Great :)
thanks buddy.. it sounds good...
ULTIMATE SLOUTION
My Solution
SELECT var FROM `test` ORDER BY CAST(var as SIGNED)
Thanks dude...
it really helps me..
And save a lot of time and money...
Thanks
ESP
Thanx for this tip! it saved my day ;] works really good when you have number+character :)
Muchas gracias!!
No sabia como resolverlo
I have tried to finding a solution to sort TEXT so this Is gr8 and use Full Thanks buddy
I've searched all over the internet to find some solution for varchar field with mixed numbers and text and it works perfect! (Fortunately all my records in this field starts with number and text is after)
Big THX!
hayz, i tried the syntax above but it seems doesn't work in numbers + alphabet. hayz. it gives me a headache.
Well, the strings aren't sorted at all but only the numbers. If this is your problem you may want to try:
mysql> SELECT column FROM table_name ORDER BY column+0, column;
This will sort the strings alphabetically followed by the numbers, sorted numerically.
regards,
Ben.
Exactly what I needed,
mysql> SELECT column FROM table_name ORDER BY column+0, column;
Case 1: column is a number (contained in varchar)
column+0 casts it to number type so the ordering is done by numeric value of column (the second order column is a string and only considered in case of duplicates)
Case 2: column is a text (contained in varchar) then column+0 casts to 0(zero), equals for all rows, so the ordering is demanded to second column, text ordering is done.
Case 3: column is number in number field, nothing special
Case 4: column is a text in number field, basically not possible, in case of casting due to some expression its 0(zero)
Thanks Ben
Claudio Nanni
Spot on what I wanted
Wonderful!
Very useful solution, tanks for the trick.
but this fails when the data in the varchar field is a combination of alphabet and numeric
Yarr, using varchar fields to store numerical data is already a clear indicator that your database design is flawed.
I have a field in my database that is item items unique reference. Coming from different suppliers, the items have various alphanumeric ID's which cannot be changed to stop the database design being flawed.
Basically, some codes are just numeric, some are alphanumeric with a number first then the letter(s), some vice versa. Some may even have a combination of letters then numbers or vice versa.
X1, X2, X3....X10, 1, 2, 3, 1A, 1B, 1C
I've tried
ORDER BY col_name+0, col_name
ORDER BY col_name, col_name+0
ORDER BY ABS(col_name), col_name+0, col_name
as well as other combinations. One way will order the X1, X2, X3....X10 etc correctly as well as the 1, 2, 3 but the 1A, 1B, 1C are not in order. One way will order the 1, 2, 3 and 1A, 1B, 1C correctly by then order the X1, X2, X3 as X1, X10, X2, X3 etc.
It's driving me crazy! Please help!
I haven't tried it yet but this might be useful for situations like mine where I have varchar fields with filenames that are numbered such as filename_01.jpg, filename_02.jpg, etc. I don't know if this is what I need, but it doesn't mean the database is flawed necessarily.