MySQL Order By - sorting alphanumeric correctly

Question

I want to sort the following data items in the order they are presented below  numbers 1-12     1 2 3 4 5 6 7 8 9 10 11 12  However  my query - using order by xxxxx asc sorts by the first digit above all else    1 10 11 12 2 3 4 5 6 7 8 9  Any tricks to make it sort more properly   Further  in the interest of full disclosure  this could be a mix of letters and numbers  although right now it is not   e g     A1 534G G46A 100B 100A 100JE   etc      Thanks   update  people asking for query  select   from table order by name asc

User · Answer

This should sort alphanumeric field like: 1/ Number only, order by 1,2,3,4,5,6,7,8,9,10,11 etc... 2/ Then field with text like: 1foo, 2bar, aaa11aa, aaa22aa, b5452 etc...

SELECT  MyField
FROM MyTable
order by 
    IF( MyField REGEXP '^-?[0-9]+$' = 0, 
    9999999999 ,  
    CAST(MyField AS DECIMAL) 
    ), MyField

The query check if the data is a number, if not put it to 9999999999 , then order first on this column, then order on data with text

Good luck!

User · Answer

Instead of trying to write some function and slow down the SELECT query  I thought of another way of doing this     Create an extra field in your database that holds the result from the following Class and when you insert a new row  run the field value that will be naturally sorted through this class and save its result in the extra field   Then instead of sorting by your original field  sort by the extra field   String nsFieldVal   new NaturalSortString getFieldValue     4  toString    The above means  - Create a NaturalSortString for the String returned from getFieldValue   - Allow up to 4 bytes to store each character or number  4 bytes   ffff   65535     field 32      nsfield 161                                    a1            300610001     String sortString   new NaturalSortString getString    4  toString    import StringUtils          Creates a string that allows natural sorting in a SQL database    eg  0 1 1a 2 3 3a 10 100 a a1 a1a1 b     public class NaturalSortString        private String inStr      private int byteSize      private StringBuilder out   new StringBuilder                    A byte stores the hex value  0 to f  of a letter or number         Since a letter is two bytes  the minimum byteSize is 2                2 bytes   00 - ff   max number is 255         3 bytes   000 - fff  max number is 4095         4 bytes   0000 - ffff  max number is 65535                For example         dog123   64 6F 67 7B and thus byteSize  gt   2               dog280   64 6F 67 118 and thus byteSize  gt   3                For example         The String   There are 1000000 spots on a dalmatian  would require a byteSize that can         store the number  1000000  which in hex is  f4240  and thus the byteSize must be at least 5               The dbColumn size to store the NaturalSortString is calculated as          gt  originalStringColumnSize x byteSize   1        The extra  1  is a marker for String type - Letter  Number  Symbol        Thus  if the originalStringColumn is varchar 32  and the byteSize is 5          gt  NaturalSortStringColumnSize   32 x 5   1   varchar 161                The byteSize must be the same for all NaturalSortStrings created in the same table         If you need to change the byteSize  for instance  to accommodate larger numbers   you will        need to recalculate the NaturalSortString for each existing row using the new byteSize                 param str        String to create a natural sort string from         param byteSize   Per character storage byte size  minimum 2          throws Exception See the error description thrown             public NaturalSortString String str  int byteSize  throws Exception           if  str    null    str isEmpty    return          this inStr   str          this byteSize   Math max 2  byteSize       minimum of 2 bytes to hold a character         setStringType            iterateString               private void setStringType             char firstchar   inStr toLowerCase   subSequence 0  1  charAt 0           if  Character isLetter firstchar          letters third             out append 3           else if  Character isDigit firstchar      numbers second             out append 2           else                                      non-alphanumeric first             out append 1              private void iterateString   throws Exception           StringBuilder n   new StringBuilder            for  char c   inStr toLowerCase   toCharArray         lowercase for CASE INSENSITIVE sorting             if  Character isDigit c                        group numbers                 n append c                   continue                            if  n length    gt  0                    addInteger n toString                     n   new StringBuilder                              addCharacter c                     if  n length    gt  0                addInteger n toString                          private void addInteger String s  throws Exception           int i   Integer parseInt s           if  i  gt    Math pow 16  byteSize                throw new Exception  naturalsort bytesize exceeded            out append StringUtils padLeft Integer toHexString i   byteSize               private void addCharacter char c              TODO  Add rest of accented characters         if  c  gt   224  amp  amp  c  lt   229     set accented a to a             c    a           else if  c  gt   232  amp  amp  c  lt   235     set accented e to e             c    e           else if  c  gt   236  amp  amp  c  lt   239     set accented i to i             c    i           else if  c  gt   242  amp  amp  c  lt   246     set accented o to o             c    o           else if  c  gt   249  amp  amp  c  lt   252     set accented u to u             c    u           else if  c  gt   253  amp  amp  c  lt   255     set accented y to y             c    y            out append StringUtils padLeft Integer toHexString c   byteSize                Override     public String toString             return out toString              For completeness  below is the StringUtils padLeft method   public static String padLeft String s  int n        if  n - s length      0  return s      return String format   0     n - s length       d s   0  s       The result should come out like the following  -1 -a 0 1 1 0 1 01 1 1 1 1a 1b 9 10 10a 10ab 11 12 12abcd 100 a a1a1 a1a2 a-1 a-2   viacion b c1 c2 c12 c100 d d1 1 1 e

User · Answer

I had some good results with   SELECT alphanumeric  integer FROM sorting test ORDER BY CAST alphanumeric AS UNSIGNED   alphanumeric ASC

User · Answer

Just do this   SELECT   FROM table ORDER BY column  name  0 ASC   Appending the  0 will mean that   0  10  11  2  3  4  becomes    0  2  3  4  10  11

User · Answer

SELECT length actual project name  actual project name  SUBSTRING INDEX actual project name  -  1  as aaaaaa  SUBSTRING INDEX actual project name   -   -1  as actual project number  concat SUBSTRING INDEX actual project name  -  1  SUBSTRING INDEX actual project name   -   -1   as a FROM ctts test22  order by  SUBSTRING INDEX actual project name  -  1  asc cast SUBSTRING INDEX actual project name   -   -1  as unsigned  asc

User · Answer

This type of question has been asked previously   The type of sorting you are talking about is called  Natural Sorting   The data on which you want to do sort is alphanumeric  It would be better to create a new column for sorting   For further help check  natural-sort-in-mysql

User · Answer

If you need to sort an alpha-numeric column that does not have any standard format whatsoever  SELECT   FROM table ORDER BY  name    0   DESC   name 0  gt  0  DESC  name 0 ASC  name ASC   You can adapt this solution to include support for non-alphanumeric characters if desired using additional logic

User · Answer

MySQL ORDER BY Sorting alphanumeric on correct order example  SELECT  alphanumericCol  FROM  tableName  ORDER BY    SUBSTR  alphanumericCol  FROM 1 FOR 1      LPAD lower  alphanumericCol    10 0  ASC  output  1 2 11 21 100 101 102 104 S-104A S-105 S-107 S-111

User · Answer

This works for type of data     Data1   Data2  Data3        Data21  Means  Data  String is common in all rows    For ORDER BY ASC it will sort perfectly  For ORDER BY DESC not suitable   SELECT   FROM table name ORDER BY LENGTH column name   column name ASC

User · Answer

This is a simple example  SELECT HEX some col  h         FROM some table  ORDER BY h

User · Answer

SELECT  s id  s name   LENGTH s name  len  ASCII s name  ASCCCI FROM table name s ORDER BY ASCCCI len NAME ASC

User · Answer

Try this For ORDER BY DESC  SELECT   FROM testdata ORDER BY LENGHT name  DESC  name DESC

User · Answer

People use different tricks to do this  I Googled and find out some results each follow different tricks  Have a look at them    Alpha Numeric Sorting in MySQL Natural Sorting in MySQL Sorting of numeric values mixed with alphanumeric values mySQL natural sort Natural Sort in MySQL   Edit   I have just added the code of each link for future visitors   Alpha Numeric Sorting in MySQL  Given input  1A 1a 10A 9B 21C 1C 1D  Expected output  1A 1C 1D 1a 9B 10A 21C  Query  Bin Way                                     SELECT  tbl column   BIN tbl column  AS binray not needed column FROM db table ORDER BY binray not needed column ASC   tbl column ASC  -----------------------  Cast Way                                     SELECT  tbl column   CAST tbl column as SIGNED  AS casted column FROM db table ORDER BY casted column ASC   tbl column ASC   Natural Sorting in MySQL  Given input  Table  sorting test  -------------------------- -------------   alphanumeric VARCHAR 75    integer INT    -------------------------- -------------   test1                      1               test12                     2               test13                     3               test2                      4               test3                      5              -------------------------- -------------   Expected Output   -------------------------- -------------   alphanumeric VARCHAR 75    integer INT    -------------------------- -------------   test1                      1               test2                      4               test3                      5               test12                     2               test13                     3              -------------------------- -------------   Query  SELECT alphanumeric  integer        FROM sorting test        ORDER BY LENGTH alphanumeric   alphanumeric     Sorting of numeric values mixed with alphanumeric values  Given input  2a  12  5b  5a  10  11  1  4b   Expected Output  1  2a  4b  5a  5b  10  11  12   Query  SELECT version FROM version sorting ORDER BY CAST version AS UNSIGNED   version    Hope this helps

User · Answer

I know this post is closed but I think my way could help some people  So there it is    My dataset is very similar but is a bit more complex  It has numbers  alphanumeric data    1 2 Chair  3 0 4 5 - Table 10 13 19 Windows 99 102 Dog   I would like to have the  -  symbol at first  then the numbers  then the text   So I go like this    SELECT name   name    -   boolDash   name    0   boolZero   name 0  gt  0  boolNum  FROM table  ORDER BY boolDash DESC  boolZero DESC  boolNum DESC   name 0   name   The result should be something    - 0     1 2 3 4 5 10 13 99 102 Chair Dog Table Windows   The whole idea is doing some simple check into the SELECT and sorting with the result

User · Answer

I hate this  but this will work  order by lpad name  10  0    lt -- assuming maximum string length is 10                              lt -- you can adjust to a bigger length if you want to

[mysql] MySQL 'Order By' - sorting alphanumeric correctly

Examples related to mysql

Examples related to sql-order-by