SQL 根据字符串里的数字排序|极客教程

SQL 根据字符串里的数字排序，你希望根据字符串里的数字对结果集进行排序，考虑下面的视图。

SQL 根据字符串里的数字排序问题描述

你希望根据字符串里的数字对结果集进行排序，考虑下面的视图。

create view V as
select e.ename ||' '||
       cast(e.empno as char(4))||' '||
       d.dname as data
  from emp e, dept d
 where e.deptno=d.deptno

下面是上述视图返回的数据。

DATA
-----------------------------
CLARK   7782 ACCOUNTING
KING    7839 ACCOUNTING
MILLER  7934 ACCOUNTING
SMITH   7369 RESEARCH
JONES   7566 RESEARCH
SCOTT   7788 RESEARCH
ADAMS   7876 RESEARCH
FORD    7902 RESEARCH
ALLEN   7499 SALES
WARD    7521 SALES
MARTIN  7654 SALES
BLAKE   7698 SALES
TURNER  7844 SALES
JAMES   7900 SALES

以上数据包括员工名字、员工编号和部门名称三部分数据，你希望按照中间的员工编号对该数据进行排序。

DATA
----------------------------
SMITH   7369 RESEARCH
ALLEN   7499 SALES
WARD    7521 SALES
JONES   7566 RESEARCH
MARTIN  7654 SALES
BLAKE   7698 SALES
CLARK   7782 ACCOUNTING
SCOTT   7788 RESEARCH
KING    7839 ACCOUNTING
TURNER  7844 SALES
ADAMS   7876 RESEARCH
JAMES   7900 SALES
FORD    7902 RESEARCH
MILLER  7934 ACCOUNTING

SQL 根据字符串里的数字排序解决方案

下面的每一种解决方案都使用了各个数据库特有的函数和语法，但方法（利用内置函数 REPLACE 和 TRANSLATE）却是相同的。基本思路都是使用 REPLACE 和 TRANSLATE 函数删除字符串里的非数字字符，只留下用于排序的数字。
DB2
使用内置函数 REPLACE 和 TRANSLATE 提取字符串里的数字，并按照数字排序。

1 select data
2   from V
3  order by
4         cast(
5      replace(
6    translate(data,repeat('#',length(data)),
7      replace(
8    translate(data,'##########','0123456789'),
9             '#','')),'#','') as integer)

Oracle
使用内置函数 REPLACE 和 TRANSLATE 提取字符串里的数字，并按照数字排序。

1 select data
2   from V
3  order by
4         to_number(
5           replace(
6         translate(data,
7           replace(
8         translate(data,'0123456789','##########'),
9                  '#'),rpad('#',20,'#')),'#'))

PostgreSQL
使用内置函数 REPLACE 和 TRANSLATE 提取字符串里的数字，并按照数字排序。

1 select data
2   from V
3  order by
4         cast(
5      replace(
6    translate(data,
7      replace(
8    translate(data,'0123456789','##########'),
9             '#',''),rpad('#',20,'#')),'#','') as integer)

MySQL 和 SQL Server
在写作本书时，这两种数据库尚不支持 TRANSLATE 函数。

SQL 根据字符串里的数字排序扩展知识

视图 V 只是为了提供演示本实例解决方案的数据。该视图只是简单地把 EMP 表的一些列串联起来。上述解决方案展示了如何以串联后的文本作为输入数据，并按照嵌入其中的员工编号进行排序。
各个解决方案的 ORDER BY 子句虽然看起来有点吓人，但效果不错。如果我们一段一段地仔细阅读的话，就会发现它其实不难理解。为了按照字符串里的数字排序，最简单的办法就是删除所有的非数字字符。删除非数字字符后，把数字字符变成数值类型，并进行排序。在开始解释每一次函数调用之前，我们要先理解各个函数被调用的次序。先从最内层的 TRANSLATE 函数调用（每个解决方案的第 8 行）开始，我们可以看到：
(1) TRANSLATE 函数（第 8 行）被调用，把执行结果传递给；
(2) REPLACE 函数（第 7 行），并把执行结果传递给；
(3) TRANSLATE 函数（第 6 行），并把执行结果传递给；
(4) REPLACE 函数（第 5 行），其执行结果被返回，最后；
(5) 转换为数值类型。
第一步是把数字替换为一个特别的字符，它和去掉数字后字符串里剩下的字符都不相同。本例中我选择了 #，并使用 TRANSLATE 函数把所有的数字都替换成 #。例如，下面的查询左边显示的是原来的字符串，右边显示的是第一次转换后得到的结果。

select data,
       translate(data,'0123456789','##########') as tmp
  from V
　
DATA                           TMP
 ------------------------------ -----------------------
 CLARK   7782 ACCOUNTING        CLARK   #### ACCOUNTING
 KING    7839 ACCOUNTING        KING    #### ACCOUNTING
 MILLER  7934 ACCOUNTING        MILLER  #### ACCOUNTING
 SMITH   7369 RESEARCH          SMITH   #### RESEARCH
 JONES   7566 RESEARCH          JONES   #### RESEARCH
 SCOTT   7788 RESEARCH          SCOTT   #### RESEARCH
 ADAMS   7876 RESEARCH          ADAMS   #### RESEARCH
 FORD    7902 RESEARCH          FORD    #### RESEARCH
 ALLEN   7499 SALES             ALLEN   #### SALES
 WARD    7521 SALES             WARD    #### SALES
 MARTIN  7654 SALES             MARTIN  #### SALES
 BLAKE   7698 SALES             BLAKE   #### SALES
 TURNER  7844 SALES             TURNER  #### SALES
 JAMES   7900 SALES             JAMES   #### SALES

TRANSLATE 函数找到每个字符串里的数字字符，并逐一替换为 #。转换后字符串被传递到 REPLACE 函数（第 7 行），它会删除所有的 #。

select data,
replace(
translate(data,'0123456789','##########'),'#') as tmp
  from V
　
DATA                           TMP
 ------------------------------ -----------------------
 CLARK   7782 ACCOUNTING        CLARK    ACCOUNTING
 KING    7839 ACCOUNTING        KING     ACCOUNTING
 MILLER  7934 ACCOUNTING        MILLER   ACCOUNTING
 SMITH   7369 RESEARCH          SMITH    RESEARCH
 JONES   7566 RESEARCH          JONES    RESEARCH
 SCOTT   7788 RESEARCH          SCOTT    RESEARCH
 ADAMS   7876 RESEARCH          ADAMS    RESEARCH
 FORD    7902 RESEARCH          FORD     RESEARCH
 ALLEN   7499 SALES             ALLEN    SALES
 WARD    7521 SALES             WARD     SALES
 MARTIN  7654 SALES             MARTIN   SALES
 BLAKE   7698 SALES             BLAKE    SALES
 TURNER  7844 SALES             TURNER   SALES
 JAMES   7900 SALES             JAMES    SALES

然后，上述结果再一次被传递给 TRANSLATE 函数，但这次是本解决方案第二次（最外层）调用 TRANSLATE 函数。该 TRANSLATE 函数在原来的字符串中搜索和 TMP 相匹配的字符。如果找到的话，就把它们都替换成 #。这一转换使得所有非数字字符能够被当作单一字符来处理（因为它们都被替换成了相同的字符）。

select data, translate(data,
             replace(
             translate(data,'0123456789','##########'),
                       '#'),
                       rpad('#',length(data),'#')) as tmp
  from V
　
DATA                           TMP
------------------------------ ---------------------------
CLARK   7782 ACCOUNTING        ########7782###########
KING    7839 ACCOUNTING        ########7839###########
MILLER  7934 ACCOUNTING        ########7934###########
SMITH   7369 RESEARCH          ########7369#########
JONES   7566 RESEARCH          ########7566#########
SCOTT   7788 RESEARCH          ########7788#########
ADAMS   7876 RESEARCH          ########7876#########
FORD    7902 RESEARCH          ########7902#########
ALLEN   7499 SALES             ########7499######
WARD    7521 SALES             ########7521######
MARTIN  7654 SALES             ########7654######
BLAKE   7698 SALES             ########7698######
TURNER  7844 SALES             ########7844######
JAMES   7900 SALES             ########7900######

接下来，通过调用 REPLACE 函数（第 5 行）删除所有的 #，只留下数字字符。

select data, replace(
             translate(data,
             replace(
           translate(data,'0123456789','##########'),
                     '#'),
                     rpad('#',length(data),'#')),'#') as tmp
  from V
　
DATA                           TMP
------------------------------ -----------
CLARK   7782 ACCOUNTING        7782
KING    7839 ACCOUNTING        7839
MILLER  7934 ACCOUNTING        7934
SMITH   7369 RESEARCH          7369
JONES   7566 RESEARCH          7566
SCOTT   7788 RESEARCH          7788
ADAMS   7876 RESEARCH          7876
FORD    7902 RESEARCH          7902
ALLEN   7499 SALES             7499
WARD    7521 SALES             7521
MARTIN  7654 SALES             7654
BLAKE   7698 SALES             7698
TURNER  7844 SALES             7844
JAMES   7900 SALES             7900

最后，使用数据库管理系统中合适的函数（通常是 CAST）把 TMP 转换为数值类型（第 4 行），结果如下所示。

select data, to_number(
              replace(
             translate(data,
             replace(
       translate(data,'0123456789','##########'),
                     '#'),
                     rpad('#',length(data),'#')),'#')) as tmp
  from V
　
DATA                                  TMP
------------------------------ ----------
CLARK   7782 ACCOUNTING              7782
KING    7839 ACCOUNTING              7839
MILLER  7934 ACCOUNTING              7934
SMITH   7369 RESEARCH                7369
JONES   7566 RESEARCH                7566
SCOTT   7788 RESEARCH                7788
ADAMS   7876 RESEARCH                7876
FORD    7902 RESEARCH                7902
ALLEN   7499 SALES                   7499
WARD    7521 SALES                   7521
MARTIN  7654 SALES                   7654
BLAKE   7698 SALES                   7698
TURNER  7844 SALES                   7844
JAMES   7900 SALES                   7900

当编写类似这样的查询语句时，不妨把写好的表达式放入 SELECT 列表里试着执行一下，这会非常有用。因为我们能很容易看到中间结果，直到得出最终的解决方案。然而，因为本实例的重点是对结果集进行排序，所以最终仍需要把所有的函数调用都放进 ORDER BY 子句里。

select data
  from V
 order by
        to_number(
          replace(
        translate( data,
          replace(
        translate( data,'0123456789','##########'),
                  '#'),rpad('#',length(data),'#')),'#'))
　
DATA
---------------------------
SMITH   7369 RESEARCH
ALLEN   7499 SALES
WARD    7521 SALES
JONES   7566 RESEARCH
MARTIN  7654 SALES
BLAKE   7698 SALES
CLARK   7782 ACCOUNTING
SCOTT   7788 RESEARCH
KING    7839 ACCOUNTING
TURNER  7844 SALES
ADAMS   7876 RESEARCH
JAMES   7900 SALES
FORD    7902 RESEARCH
MILLER  7934 ACCOUNTING

最后值得注意的是，本例的视图数据包含 3 个字段，其中只有一个字段是数字。如果有多个数字字段，就需要将它们拼接成一个数字，然后再排序。