• Saturday, May 05, 2012
  • Sorting Alphanumeric values in MySQL


    Yesterday I got query from a visitor of my blog, he wants to do a sorting were the data stored in the field is alphanumeric. Normally this type of sorting we call it as "Natural Sort". He also gave me the structure of the data.



    Data
    Here is an example how the column looks.

    trip_id
    TRPAN1000
    TRPAN999
    TRPAN1002
    TRPAN998

    Now he wants to sort the above column in following order.

    trip_id
    TRPAN1002
    TRPAN1000
    TRPAN999
    TRPAN998

    Problem

     SELECT trip_id FROM test_table ORDER BY trip_id DESC 

    trip_id
    TRPAN1000
    TRPAN1002
    TRPAN998
    TRPAN999

    Sorting by alphanumeric column with normal ORDER BY query will end in unexpected result.

    Solution
    Since first 5 characters are common in trip_id field, I have used SUBSTR function to remove those characters and then used CAST function to sort the integer values which is in the form of varchar datatype.

     SELECT trip_id FROM test_table ORDER BY CAST(SUBSTR(trip_id,6) AS SIGNEDDESC 

    Here is our expected result.

    trip_id
    TRPAN1002
    TRPAN1000
    TRPAN999
    TRPAN998


    Share this post

    Kathirason Asokan
    About the Author
    I'm , I love writing code in PHP, and spending time with friends. My hobbies are blogging, surfing internet, juggling etc.,.

    2 comments:

    1. Hey Hi!!
      I would like to know the Datatype to be used to accept Alphanumeric Data Values in MYSQL to accept it in ID Field....like MC1000

      ReplyDelete

     
    Copyright (c) 2011 - 2021 techispeaks.com. All rights reserved the content is copyrighted to Kathirason Asokan
    Creative Commons License