Sorting string column containing numbers in SQL?

by

Going on the assumption it’s always WORD_space_NUMBER this should work:

SELECT   *
FROM     table
ORDER BY CAST(SUBSTRING(column,LOCATE(' ',column)+1) AS SIGNED)

Use POSITION to find the space, SUBSTRING to grab the number after it, and CAST to make it a comparable value.

If there is a different pattern to the column, let me know and I’ll try to devise a better work-around.

EDIT Proven to work:

mysql> INSERT INTO t (st) VALUES ('a 1'),('a 12'),('a 6'),('a 11');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t ORDER BY st;
+----+------+
| id | st   |
+----+------+
|  1 | a 1  |
|  4 | a 11 |
|  2 | a 12 |
|  3 | a 6  |
+----+------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM t ORDER BY CAST(SUBSTRING(st,LOCATE(' ',st)+1) AS SIGNED);
+----+------+
| id | st   |
+----+------+
|  1 | a 1  |
|  3 | a 6  |
|  4 | a 11 |
|  2 | a 12 |
+----+------+

mysql> INSERT INTO t (st) VALUES ('b 1'),('b 12'),('b 6'),('b 11');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t ORDER BY CAST(SUBSTRING(st,LOCATE(' ',st)+1) AS SIGNED);
+----+------+
| id | st   |
+----+------+
|  1 | a 1  |
|  5 | b 1  |
|  3 | a 6  |
|  7 | b 6  |
|  4 | a 11 |
|  8 | b 11 |
|  2 | a 12 |
|  6 | b 12 |
+----+------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM t ORDER BY LEFT(st,LOCATE(' ',st)), CAST(SUBSTRING(st,LOCATE(' ',st)+1) AS SIGNED);
+----+------+
| id | st   |
+----+------+
|  1 | a 1  |
|  3 | a 6  |
|  4 | a 11 |
|  2 | a 12 |
|  5 | b 1  |
|  7 | b 6  |
|  8 | b 11 |
|  6 | b 12 |
+----+------+
8 rows in set (0.00 sec)

ignore my lame table/column names, but gives me the correct result. Also went a little further and added double sort to break letters prefix with numeric.

Edit
SUBSTRING_INDEX will make it little more readable.

ORDER BY SUBSTRING_INDEX(st, " ", 1) ASC, CAST(SUBSTRING_INDEX(st, " ", -1) AS SIGNED)

More Related Contents:

  1. MySQL: Alternatives to ORDER BY RAND()
  2. How to ORDER BY a SUM() in MySQL?
  3. MySQL LIKE IN()?
  4. Auto Increment after delete in MySQL
  5. Unioning two tables with different number of columns
  6. PostgreSQL DISTINCT ON with different ORDER BY
  7. NULL in MySQL (Performance & Storage)
  8. In MySQL queries, why use join instead of where?
  9. MySQL Trigger to prevent INSERT under certain conditions
  10. Importance of varchar length in MySQL table
  11. Stored Procedure with optional “WHERE” parameters
  12. SQLite – ORDER BY RAND()
  13. How to search multiple columns in MySQL?
  14. How do I return rows with a specific value first?
  15. SQL (ORACLE): ORDER BY and LIMIT [duplicate]
  16. MySQL – Selecting data from multiple tables all with same structure but different data
  17. Can a stored procedure/function return a table?
  18. Adding a new SQL column with a default value
  19. Multiple Ranks in one table
  20. Create date from day, month, year fields in MySQL
  21. The difference between ‘AND’ and ‘&&’ in SQL
  22. Can I use a MySQL database with an App Engine application
  23. How can I make an average of dates in MySQL?
  24. What is the best way to generate ranks in MYSQL?
  25. What does collation mean?
  26. What’s the correct name for an “association table” (a many-to-many relationship) [closed]
  27. Curious issue with Oracle UNION and ORDER BY
  28. sql pulling a row for next or previous row of a current row
  29. Row Rank in a MySQL View
  30. Select Rows with Maximum Column Value group by Another Column

Leave a Comment