Home » Blogs » sandip's blog

Sorting VARCHAR data in mysql

Submitted by sandip on Thu, 01/26/2006 - 11:10

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.

Bookmark/Search this post with
  • del.icio.us
  • Digg
  • Facebook
  • identi.ca
  • LinkedIn
  • SlashDot
  • StumbleUpon
  • Technorati
  • Twitter
»

Comment viewing options

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

Thank You so much

Submitted by Anonymous on Fri, 11/07/2014 - 11:35.

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

Submitted by Anonymous on Thu, 08/09/2012 - 02:36.

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

»

awesome!! :)

Submitted by Anonymous on Sat, 11/26/2011 - 08:08.

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

THANKS BRO!! :D
dootzky

»

Abso-freakin'-wonderful!

Submitted by Anonymous on Fri, 09/30/2011 - 15:42.

Bang! Nice job ... awesome.

»

WOW!

Submitted by Anonymous on Thu, 07/07/2011 - 17:32.

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

Submitted by Anonymous on Sat, 06/18/2011 - 07:21.

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

»

hi

Submitted by Anonymous on Fri, 04/08/2011 - 09:55.

@ULTIMATE

your solution worked for me in SQL

thanks a lot

»

Thanks

Submitted by Anonymous on Thu, 10/28/2010 - 04:48.

It works Great :)

»

thanks

Submitted by Anonymous on Mon, 08/09/2010 - 02:14.

thanks buddy.. it sounds good...

»

ULTIMATE

Submitted by Anonymous on Mon, 12/21/2009 - 09:01.

ULTIMATE SLOUTION

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

»

hi

Submitted by Anonymous on Sat, 05/09/2009 - 04:17.

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

»

Thank you very much!

Submitted by Anonymous on Thu, 12/18/2008 - 03:55.

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

»

S=cuder=O

Submitted by Anonymous on Tue, 11/18/2008 - 14:02.

Muchas gracias!!
No sabia como resolverlo

»

That is kooo

Submitted by Anonymous on Sat, 08/23/2008 - 08:52.

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

Submitted by Anonymous on Tue, 08/05/2008 - 02:47.

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

Submitted by Anonymous on Mon, 03/17/2008 - 00:21.

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

Submitted by Anonymous on Tue, 01/13/2009 - 11:28.

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

Submitted by Anonymous on Fri, 09/09/2011 - 09:15.

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

Submitted by Anonymous on Fri, 03/12/2010 - 07:33.

Spot on what I wanted

»

Thanks

Submitted by Anonymous on Wed, 10/07/2009 - 04:51.

Wonderful!

»

Sorting VARCHAR

Submitted by ulifigueroa on Tue, 08/01/2006 - 19:25.

Very useful solution, tanks for the trick.

»

but this fails when the data

Submitted by Anu (not verified) on Tue, 06/26/2007 - 08:28.

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

»

Yarr, using varchar fields

Submitted by Anonymous (not verified) on Fri, 02/01/2008 - 10:43.

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

Submitted by Anonymous on Fri, 11/13/2009 - 10:00.

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

Submitted by Anonymous (not verified) on Fri, 02/08/2008 - 11:04.

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