Sorting VARCHAR data in mysql

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
10000
200
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.

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
Thank You so much

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

Thanks a lot dude.. Brilliant idea, works like a charm. Big hug from Northen part of India.. :)

awesome!! :)

dude - that worked PERFECTLY!! :D
If you ever visit Seriba (Europe) - I'll buy you a beer!! ^_^

THANKS BRO!! :D
dootzky

Abso-freakin'-wonderful!

Bang! Nice job ... awesome.

WOW!

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 ;)

Thanks

It worked a treat in my mysql script - so easy when you know how - or someone tells you!

hi

@ULTIMATE

your solution worked for me in SQL

thanks a lot

Thanks

It works Great :)

thanks

thanks buddy.. it sounds good...

ULTIMATE

ULTIMATE SLOUTION

My Solution
SELECT var FROM `test` ORDER BY CAST(var as SIGNED)

hi

Thanks dude...
it really helps me..
And save a lot of time and money...
Thanks
ESP

Thank you very much!

Thanx for this tip! it saved my day ;] works really good when you have number+character :)

S=cuder=O

Muchas gracias!!
No sabia como resolverlo

That is kooo

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

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!

NEED HELP SORTING VARCHAR

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

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.

Ben is the man, column+0,column is the solution

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

Cheers

Spot on what I wanted

Thanks

Wonderful!

Sorting VARCHAR

Very useful solution, tanks for the trick.

but this fails when the data

but this fails when the data in the varchar field is a combination of alphabet and numeric

Yarr, using varchar fields

Yarr, using varchar fields to store numerical data is already a clear indicator that your database design is flawed.

Not flawed - but still not ordering correctly

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

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.

Comment