在SQL中对包含数字的字符串列进行排序？

在SQL中对包含数字的字符串列进行排序？

继续 假设 它始终为WORD_space_NUMBER，这应该可行：

SELECt   *FROM     tableORDER BY CAST(SUBSTRINg(column,LOCATE(' ',column)+1) AS SIGNED)

使用POSITION查找空间，使用SUBSTRING捕获其后的数字，然后使用CAST使其具有可比较的值。

如果该列有另一种模式，请告诉我，我将尝试设计一种更好的解决方法。

---

编辑 证明有效：

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: 0mysql> 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: 0mysql> 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)

忽略我的la脚表/列名，但会给我正确的结果。还走得更远，添加了双重排序功能，以数字打破字母前缀。

编辑

SUBSTRING_INDEX

将使其更具可读性。

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