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:

sandip's blog

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.

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.