SQL 分离数字和字符数据|极客教程

SQL 分离数字和字符数据，你很不幸地把数字和字符数据混合存放进一列。你想把其中的数字数据和字符数据分开，考虑如下的结果集。

SQL 分离数字和字符数据问题描述

你很不幸地把数字和字符数据混合存放进一列。你想把其中的数字数据和字符数据分开，考虑如下的结果集。

DATA
---------------
SMITH800
ALLEN1600
WARD1250
JONES2975
MARTIN1250
BLAKE2850
CLARK2450
SCOTT3000
KING5000
TURNER1500
ADAMS1100
JAMES950
FORD3000
MILLER1300

你希望得到如下的结果集。

ENAME             SAL
---------- ----------
SMITH             800
ALLEN            1600
WARD             1250
JONES            2975
MARTIN           1250
BLAKE            2850
CLARK            2450
SCOTT            3000
KING             5000
TURNER           1500
ADAMS            1100
JAMES             950
FORD             3000
MILLER           1300

SQL 分离数字和字符数据解决方案

使用内置函数 TRANSLATE 和 REPLACE 来分离字符数据和数字数据。与本章的其他实例类似，此处的技巧在于使用 TRANSLATE 函数把多种字符替换成一个指定的字符。这样一来只要用一个数字就能代表所有数字，一个字符就能代表所有字符，因此我们就不再需要逐一查找多个数字或字符了。
DB2
使用 TRANSLATE 和 REPLACE 函数分离数字和字符数据。

1 select replace(
2      translate(data,'0000000000','0123456789'),'0','') ename,
3           cast(
4        replace(
5      translate(lower(data),repeat('z',26),
6            'abcdefghijklmnopqrstuvwxyz'),'z','') as integer) sal
7     from (
8   select ename||cast(sal as char(4)) data
9     from emp
10           ) x

Oracle
使用 TRANSLATE 和 REPLACE 函数分离数字和字符数据。

 1  select replace(
 2       translate(data,'0123456789','0000000000'),'0') ename,
 3       to_number(
 4         replace(
 5         translate(lower(data),
 6                   'abcdefghijklmnopqrstuvwxyz',
 7                    rpad('z',26,'z')),'z')) sal
 8    from (
 9  select ename||sal data
10    from emp
11         )

PostgreSQL
使用 TRANSLATE 和 REPLACE 函数分离数字和字符数据。

 1 select replace(
 2      translate(data,'0123456789','0000000000'),'0','') as ename,
 3           cast(
 4        replace(
 5      translate(lower(data),
 6                'abcdefghijklmnopqrstuvwxyz',
 7                rpad('z',26,'z')),'z','') as integer) as sal
 8   from (
 9 select ename||sal as data
10   from emp
11        ) x

SQL 分离数字和字符数据扩展知识

每个数据库管理系统的语法都略有不同，但方法是一样的。在本节的讨论中，我将以 Oracle 解决方案为主。解决本问题的关键在于分离数字和字符数据，使用 TRANSLATE 和 REPLACE 函数可以实现这一点。为了提取数字，首先用 TRANSLATE 函数把所有的字符数据分离出来。

select data,
       translate(lower(data),
                'abcdefghijklmnopqrstuvwxyz',
                 rpad('z',26,'z')) sal
  from (select ename||sal data from emp)
　
DATA                 SAL
-------------------- -------------------
SMITH800             zzzzz800
ALLEN1600            zzzzz1600
WARD1250             zzzz1250
JONES2975            zzzzz2975
MARTIN1250           zzzzzz1250
BLAKE2850            zzzzz2850
CLARK2450            zzzzz2450
SCOTT3000            zzzzz3000
KING5000             zzzz5000
TURNER1500           zzzzzz1500
ADAMS1100            zzzzz1100
JAMES950             zzzzz950
FORD3000             zzzz3000
MILLER1300           zzzzzz1300

使用 TRANSLATE 函数把每一个非数字字符都替换为小写字母 z。然后使用 REPLACE 函数删除所有的小写字母 z，这样就只留下数字字符，我们可以将其转换为一个数字。

select data,
       to_number(
         replace(
       translate(lower(data),
                 'abcdefghijklmnopqrstuvwxyz',
                  rpad('z',26,'z')),'z')) sal
  from (select ename||sal data from emp)
　
DATA                        SAL
-------------------- ----------
SMITH800                    800
ALLEN1600                  1600
WARD1250                   1250
JONES2975                  2975
MARTIN1250                 1250
BLAKE2850                  2850
CLARK2450                  2450
SCOTT3000                  3000
KING5000                   5000
TURNER1500                 1500
ADAMS1100                  1100
JAMES950                    950
FORD3000                   3000
MILLER1300                 1300

为了提取非数字字符，需要使用 TRANSLATE 函数隔离数字字符。

select data,
       translate(data,'0123456789','0000000000') ename
  from (select ename||sal data from emp)
　
DATA                 ENAME
-------------------- ----------
SMITH800             SMITH000
ALLEN1600            ALLEN0000
WARD1250             WARD0000
JONES2975            JONES0000
MARTIN1250           MARTIN0000
BLAKE2850            BLAKE0000
CLARK2450            CLARK0000
SCOTT3000            SCOTT0000
KING5000             KING0000
TURNER1500           TURNER0000
ADAMS1100            ADAMS0000
JAMES950             JAMES000
FORD3000             FORD0000
MILLER1300           MILLER0000

使用 TRANSLATE 函数把每一个数字字符替换为 0，然后使用 REPLACE 函数删除每条记录中出现的 0，剩下的就只有非数字字符。

select data,
       replace(translate(data,'0123456789','0000000000'),'0') ename
  from (select ename||sal data from emp)
　
DATA                 ENAME
-------------------- --------
SMITH800             SMITH
ALLEN1600            ALLEN
WARD1250             WARD
JONES2975            JONES
MARTIN1250           MARTIN
BLAKE2850            BLAKE
CLARK2450            CLARK
SCOTT3000            SCOTT
KING5000             KING
TURNER1500           TURNER
ADAMS1100            ADAMS
JAMES950             JAMES
FORD3000             FORD
MILLER1300           MILLER