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.
Now he wants to sort the above column in following order.
Problem
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.
Here is our expected 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 SIGNED) DESC
Here is our expected result.
| trip_id |
|---|
| TRPAN1002 |
| TRPAN1000 |
| TRPAN999 |
| TRPAN998 |
Hey guys very good post and useful information
ReplyDeleteWebsite Design in Bangalore
Hey Hi!!
ReplyDeleteI would like to know the Datatype to be used to accept Alphanumeric Data Values in MYSQL to accept it in ID Field....like MC1000