Sorting string column containing numbers in SQL?
Solution 1:
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)
Solution 2:
You can try this:
ORDER BY CASE
WHEN ISNUMERIC(column) THEN cast(column as int)
else ascii(column[1,1])
end
Solution 3:
Have a look at the MySQL CAST / Convert functions.
SELECT name FROM mytable ORDER BY CAST(name AS INTEGER) ASC;
Edit: I read:
I am trying to sort string column (containing numbers).
...but just took a look at the result set. Is the a
actually also part of the contents? If so, you can use functions like MID to extract only the numeric value and cast that.
But if all rows contain just a
without variation, you might as well omit it...