Sorting string column containing numbers in SQL?

Question

I am trying to sort string column (containing numbers).

// SELECT `name` FROM `mytable` ORDER BY `name` ASC +----------+ +-- name --+ +----------+ +-- a 1 ---+ +-- a 12 --+ +-- a 2 ---+ +-- a 3 ---+ 

You see natural sorting algorithm of Mysql is placing a 12 after a 1 (which is ok for most apps), But I have unique needs, so I want result should be sorted like this.

+----------+ +-- name --+ +----------+ +-- a 1 ---+ +-- a 2 ---+ +-- a 3 ---+ +-- a 12 --+ 

Is it possible with just SQL, or I have to manipulate result-set at application level?

Answer 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) 

Answer 2

You can try this:

ORDER BY CASE   WHEN ISNUMERIC(column) THEN cast(column as int)    else ascii(column[1,1])  end