时间戳

首页 > 解决方案 > 在mysql和oracle db中将字符串排序为数字

mysql - 在mysql和oracle db中将字符串排序为数字

问题描述

我有两个数据库列（数据类型 varchar），其中包含如下值

column 1 
--------
1.8.0
1.7.0
9.0
10.0

column 2
--------
121
65
78

在 UI 上，我必须合并这两列并显示如下值

1.8.0_121
1.8.0_78

问题是在对 UI 上显示的这个组合列进行排序时。由于两列都是 varchar db order by 使用字符串发生，并且 121 出现在 78 之前，这对于升序是错误的。

作为解决方案，我在这些列上将 ABS 用于 mysql 和 to_number 用于 oracle。MySQL 对这两个列都运行良好，但 Oracle to_number 为第一列抛出错误“无效数字”，因为它的值为 1.8.0

请建议如何仅使用代码更改来处理这种情况。由于进行数据库架构更改会导致许多系统发生大量更改，因此不想触及它。

带有数据的示例表

带有数据的示例表

使用 order by 子句查询

SELECT (MAJOR || '_' || MINOR) AS VER 
FROM VERSION 
ORDER BY MAJOR ASC, MINOR ASC

结果

结果

更正了将 varchar 列视为数字的查询

SELECT (MAJOR || '_' || MINOR) AS VER 
FROM VERSION 
ORDER BY MAJOR ASC, TO_NUMBER(MINOR) ASC

修正结果

正确的结果

到目前为止，它完全没问题。但问题是 MAJOR 列可以具有 9.0 和 10.0 之类的值，所以我想将主要列也转换为 to_number 按子句的顺序，以便排序正确。但由于值是 1.8.0 它的抛出错误。

有问题的情况是

错误的结果

标签： mysqloraclesql-order-by

解决方案

一种选择是提取所有数字片段（使用正则表达式；我想说这是最简单的选择），应用于TO_NUMBER它们并按这些值排序，例如

SQL> with test (col) as
  2    (select '1.8.0' from dual union
  3     select '1.7.0' from dual union
  4     select '9.0'   from dual union
  5     select '10.0'  from dual
  6    )
  7  select col
  8  from test
  9  order by to_number(regexp_substr(col, '\d+', 1, 1)),
 10           to_number(regexp_substr(col, '\d+', 1, 2)),
 11           to_number(regexp_substr(col, '\d+', 1, 3));

COL
-----
1.7.0
1.8.0
9.0
10.0

SQL>

推荐阅读