
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)
Category: