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

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.

你看到Mysql的自然排序算法在1之后放置了12（这对大多数应用来说都没问题），但我有独特的需求，所以我想结果应该这样排序。

+----------+
+-- 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?

是否可以只使用SQL，或者我必须在应用程序级别操作结果集？

5 个解决方案

#1

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

继续假设它总是WORD_space_NUMBER这应该工作：

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.

使用POSITION查找空格，使用SUBSTRING获取后面的数字，使用CAST使其成为可比较的值。

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.

编辑SUBSTRING_INDEX会使其更具可读性。

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

#2

Have a look at the MySQL CAST / Convert functions.

看看MySQL CAST / Convert函数。

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.

...但只是看了一下结果集。 a实际上也是内容的一部分吗？如果是这样，您可以使用MID之类的函数来仅提取数值并进行转换。

But if all rows contain just a without variation, you might as well omit it...

但是如果所有行只包含一个没有变化的行，你可以省略它...

#3

You can try this:

你可以试试这个：

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

#4

Another option could be to pad the string with spaces to the left of the number (i.e. add spaces between the word and the number) and use the resulting string for sorting, comething like this:

另一种选择可能是在数字的左边用空格填充字符串（即在单词和数字之间添加空格）并使用生成的字符串进行排序，如下所示：

ORDER BY INSERT(
  column,
  LOCATE(' ', column),
  0,
  SPACES(20 - LENGTH(column) + LOCATE(' ', column))
)

The string is assumed to be of the pattern 'word followed by space(s) followed by number', and the number is assumed to be non-negative (or it would sort incorrectly). The hardcoded 20 is chosen arbitrarily and is supposed to be the string's number part's maximum possible length.

该字符串被假定为模式'单词后跟空格后跟数字'，并且该数字被假定为非负数（或者它将被错误地排序）。硬编码的20是任意选择的，并且应该是字符串的数字部分的最大可能长度。

#5

Here i found another solution with following query using Convert

在这里，我发现使用转换的以下查询的另一个解

select * from tablename where columnname like '%a%' order by Convert(smallint,Replace(columnname,'a',''))

#1