将SQL中的字符串作为数字排序

Question

我在 MySQL 数据库中将数字保存为 VARCHAR。由于其他相关情况，我无法将它们更改为 INT。

当排序时，MySQL 将它们视为字符而非数字。

在数据库中，我的数据如下：

1 2 3 4 5 6 7 8 9 10...

在我的页面中，它显示了这样的有序列表：

1 10 2 3 4 5 6 7 8 9

如何使其按数字升序排列？

- Jamol

---

@Oded 在非常特殊的情况下，您可能需要按其数值对某些任意数据（例如设置）进行排序。 - Glutexo

非常有趣的文章 - 是针对MSSQL的，但对于MySQL应该是相对类似的：https://www.essentialsql.com/use-sql-server-to-sort-alphanumeric-values/ - AquaAlex

Answer 1

如果可能的话，如果您只存储数字，那么应该将列的数据类型更改为数字。

如果您不能这样做，那么请使用以下方式将您的列值明确地转换为integer

select col from yourtable
order by cast(col as unsigned)

例如使用数学运算强制转换为数字的方式，或者隐式地进行转换

select col from yourtable
order by col + 0

顺便说一句，MySQL会从左到右转换字符串。例如：

string value  |  integer value after conversion
--------------+--------------------------------
'1'           |  1
'ABC'         |  0   /* the string does not contain a number, so the result is 0 */
'123miles'    |  123 
'$123'        |  0   /* the left side of the string does not start with a number */

- juergen d

---

按 col * 1 排序完美无缺。这其中的奥秘是什么？抱歉，我不是专业人士，所以这可能是个愚蠢的问题，但是 *1 是如何使其变成数字的呢？ - Jamol

MySQL 会自动将字符串转换为数字，以便与数字 1 进行乘法运算。 - juergen d

第一个选项是最合适的，第二个选项多了一步，但它仍然有效。 - Manatax

如果您的字符串中混合了数字和文本，并且想要对它们进行排序，请使用“order by col * 1, col”。 - Hayden Thring

@superphonic：结果将会是“十进制数”。 - juergen d

Answer 2

使用另一种方式，而不是使用单个强制转换。

(适用于使用JPA 2.0的人，其中不允许进行强制转换)

select col from yourtable
order by length(col),col

编辑：仅适用于正整数

- Velter

---

这是一个不错的例子，它适用于包含整数和字符串的列。 - Sruit A.Suk

这在所有情况下都不适用。从头脑中可以想到，不适用的情况包括：整数与负数、带前导零的数字、有小数和单词以及数字组合的混合。 - WonderWorker

很好的解决方案，无需进行任何类型转换或处理，+1。 - Maksim Luzik

一开始似乎没有起作用，但最终成功了！谢谢！ - try2fly.b4ucry

如何在此排序中指定升序或降序？ - Richard Ochom

Answer 3

另一种简单方法

按照 ABS(column_name) 排序

- Parth Solanki

Answer 4

我要排序的列包含任意组合的字母和数字，所以我以这篇文章中的建议为起点，得出了以下代码。

DECLARE @tmp TABLE (ID VARCHAR(50));
INSERT INTO @tmp VALUES ('XYZ300');
INSERT INTO @tmp VALUES ('XYZ1002');
INSERT INTO @tmp VALUES ('106');
INSERT INTO @tmp VALUES ('206');
INSERT INTO @tmp VALUES ('1002');
INSERT INTO @tmp VALUES ('J206');
INSERT INTO @tmp VALUES ('J1002');

SELECT ID, (CASE WHEN ISNUMERIC(ID) = 1 THEN 0 ELSE 1 END) IsNum
FROM @tmp
ORDER BY IsNum, LEN(ID), ID;

结果

ID
------------------------
106
206
1002
J206
J1002
XYZ300
XYZ1002

希望这能帮到你

- Losbear

---

您可以使用1-ISNUMERIC(ID)代替(CASE WHEN ISNUMERIC(ID) = 1 THEN 0 ELSE 1 END)将0和1相互转换。 - S.Serpooshan

Answer 5

这可能会帮助正在寻找相同解决方案的人。

select * from tablename ORDER BY ABS(column_name)

- Samir Lakhani

---

有点重复这个回答 https://dev59.com/9Ggt5IYBdhLWcg3wywdJ#42712796 是三年前发布的。 - RAM237

Answer 6

这对我有效。

select * from tablename
order by cast(columnname as int) asc

- Patrick De Guzman

---

你能解释一下为什么吗？ - Slavik

最简单的答案就是将字符串转换为整数，然后按升序排序，不需要任何花哨的东西 :) - denford mutseriwa

Answer 7

另一种转换方法。

如果您有字符串字段，可以通过以下方式转换它或其数字部分：添加前导零以使所有整数字符串长度相等。

ORDER BY CONCAT( REPEAT(  "0", 18 - LENGTH( stringfield ) ) , stringfield ) 

或者按字段的一部分排序，例如“tensymbols13”，“tensymbols1222”等。

ORDER BY CONCAT( REPEAT(  "0", 18 - LENGTH( LEFT( stringfield , 10 ) ) ) , LEFT( stringfield , 10 ) ) 

- shukshin.ivan

Answer 8

这个处理负数、分数、字符串等所有内容的函数：

ORDER BY ISNUMERIC(col) DESC, Try_Parse(col AS decimal(10,2)), col;

- Amir

Answer 9

我也在寻找带有字母前缀的排序字段。下面是我找到的解决方案，希望对其他需要相同解决方案的人有所帮助。

字段值:

FL01,FL02,FL03,FL04,FL05,...FL100,...FL123456789

select SUBSTRING(field,3,9) as field from table order by SUBSTRING(field,3,10)*1 desc

SUBSTRING(field,3,9)我将9放在这里是因为它足够容纳最多9位数字的整数值。

所以结果将会是 123456789 123456788 123456787 ... 100 99 ... 2 1

- user2400083

Answer 10

如果您正在使用AdonisJS并且具有混合的ID，例如ABC-202、ABC-201等，您可以将原始查询与查询构建器相结合，并按照以下方式实现上述解决方案（https://dev59.com/9Ggt5IYBdhLWcg3wywdJ#25061144）：

const sortField =
  'membership_id'
const sortDirection =
  'asc'
const subquery = UserProfile.query()
  .select(
    'user_profiles.id',
    'user_profiles.user_id',
    'user_profiles.membership_id',
    'user_profiles.first_name',
    'user_profiles.middle_name',
    'user_profiles.last_name',
    'user_profiles.mobile_number',
    'countries.citizenship',
    'states.name as state_of_origin',
    'user_profiles.gender',
    'user_profiles.created_at',
    'user_profiles.updated_at'
  )
  .leftJoin(
    'users',
    'user_profiles.user_id',
    'users.id'
  )
  .leftJoin(
    'countries',
    'user_profiles.nationality',
    'countries.id'
  )
  .leftJoin(
    'states',
    'user_profiles.state_of_origin',
    'states.id'
  )
  .orderByRaw(
    `SUBSTRING(:sortField:,3,15)*1 ${sortDirection}`,
    {
      sortField: sortField,
    }
  )
  .paginate(
    page,
    per_page
  )

注意： 在这行代码中：SUBSTRING(:sortField:,3,15)*1 ${sortDirection},

1. '3' 表示数字前最后一个非数字字符的索引号。如果您的混合 ID 是 "ABC-123"，则索引号为 4。
2. '15' 用于捕获连字符后尽可能多的数字。
3. '1' 对子字符串执行数学运算，将子字符串有效地转换为数字。

参考文献 1：您可以在此处阅读有关原始查询中参数绑定的更多信息：https://knexjs.org/#Raw-Bindings 参考文献 2：Adonis 原始查询：https://adonisjs.com/docs/4.1/query-builder#_raw_queries

- Ndianabasi