Often in PostgreSQL you may want to sort alphanumeric values (values that contain both letters and numbers) in a column of a table.
You can use the following syntax to do so:
SELECT *
FROM athletes
ORDER BY
SUBSTRING(team FROM '[a-zA-z_-]+'),
COALESCE(SUBSTRING(team FROM '(\d+)$')::INTEGER, 0);
This particular example sorts the values in the team column of the athletes table based on alphanumeric order.
The following example shows how to use this syntax in practice.
Example: How to Sort Alphanumeric Values in PostgreSQL
Suppose that we create the following table named athletes that contains information about various basketball players:
-- create table named 'athletes'
CREATE TABLE athletes (
id INTEGER PRIMARY KEY,
team TEXT,
points INTEGER
);
-- add rows to the table
INSERT INTO athletes VALUES (0001, 'Mavs22', 22);
INSERT INTO athletes VALUES (0002, 'Mavs3', 30);
INSERT INTO athletes VALUES (0003, 'Mavs17', 18);
INSERT INTO athletes VALUES (0004, 'Rockets62', 12);
INSERT INTO athletes VALUES (0005, 'Rockets41', 25);
INSERT INTO athletes VALUES (0006, 'Rockets5', 31);
-- view all rows in table
SELECT * FROM athletes;
id | team | points
----+-----------+--------
1 | Mavs22 | 22
2 | Mavs3 | 30
3 | Mavs17 | 18
4 | Rockets62 | 12
5 | Rockets41 | 25
6 | Rockets5 | 31
Notice that the values in the team column contain both letters and numbers.
Suppose that we attempt to use the ORDER BY statement to sort the table based on the values in the team column:
SELECT *
FROM athletes
ORDER BY team;
id | team | points
----+-----------+--------
1 | Mavs22 | 22
2 | Mavs3 | 30
3 | Mavs17 | 18
4 | Rockets62 | 12
5 | Rockets41 | 25
6 | Rockets5 | 31
Notice that the rows of the resulting table are not sorted in the correct order.
For example, the value Mavs22 is placed before Mavs3.
In order to correctly sort the alphanumeric values, we must use the following syntax instead:
SELECT *
FROM athletes
ORDER BY
SUBSTRING(team FROM '[a-zA-z_-]+'),
COALESCE(SUBSTRING(team FROM '(\d+)$')::INTEGER, 0);
id | team | points
----+-----------+--------
2 | Mavs3 | 30
3 | Mavs17 | 18
1 | Mavs22 | 22
6 | Rockets5 | 31
5 | Rockets41 | 25
4 | Rockets62 | 12
Notice that the rows of this table are sorted by correctly.
The values in the team column are first sorted alphabetically from A to Z, and then sorted numerically from smallest to largest.
You can use similar syntax to sort alphanumeric values in your own table.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Order by Count
PostgreSQL: How to ORDER BY Multiple Values in Specific Order
PostgreSQL: How to Use ORDER BY with NULL Values Last
