MySQL: Sorting Alpha-Numeric values with Example

In MySQL, the ORDER BY keyword is used for sorting the records ascending(ASC) or descending(DESC), by default it will be on ascending. The default ORDER BY statement will not work with Alpha-Numerical values(The values is mixed of both numbers and strings). To resolve this problem we can use ABS() MySQL function.

Absolute Value(ABS):
The ABS() will provide the absolute value for each words, based on the value we can sort Alphnumeric column as ascending or descending.

Syntax: 

SELECT COLUMN1, COLUMN2, ... FROM TABLE_NAME OREDER BY ABS(COLUMN_NAME);

Example: 

mysql> select name from userlist;
+------------+
| name       |
+------------+
| swathy23   |
| arun10     |
| mohan3     |
| arun100    |
| swathy4    |
| kaviya88   |
| gayathry8  |
| 2swathy    |
| 7arun      |
| mohan8     |
| mohan18    |
| arun20     |
| swathy17   |
| kaviya9    |
| gayathry10 |
| swathy7    |
| swathy30   |
| mohan31    |
+------------+
18 rows in set (0.00 sec)

mysql> select name from userlist ORDER BY name;
+------------+
| name       |
+------------+
| 2swathy    |
| 7arun      |
| arun10     |
| arun100    |
| arun20     |
| gayathry10 |
| gayathry8  |
| kaviya88   |
| kaviya9    |
| mohan18    |
| mohan3     |
| mohan31    |
| mohan8     |
| swathy17   |
| swathy23   |
| swathy30   |
| swathy4    |
| swathy7    |
+------------+
18 rows in set (0.00 sec)
 
mysql> select name from userlist ORDER BY ABS(name);
+------------+
| name       |
+------------+
| 2swathy    |
| 7arun      |
| arun10     |
| arun20     |
| arun100    |
| gayathry8  |
| gayathry10 |
| kaviya9    |
| kaviya88   |
| mohan3     |
| mohan8     |
| mohan18    |
| mohan31    |
| swathy4    |
| swathy7    |
| swathy17   |
| swathy23   |
| swathy30   |
+------------+
18 rows in set (0.00 sec)