将字符和数字数据分离

ehxz

问题

在一列中数字数据和字符数据混合存储在一起（真不幸），要将这些数据中的数字和字符分离出来。考虑下面列出的数据集：

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

解决方案

使用内置函数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(

5         replace(

6         translate(lower(data),

7                   'abcdefghijklmnopqrstuvwxyz',

8                    rpad('z',26,'z')),'z')) sal

9    from (

10  select ename||sal data

11    from emp

12         )

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

讨论

虽然对于每个DBMS来说，所使用的语法都有细微的差别，但是技术原理是一样的。现在用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函数，可以将每个数字字符转换为零，下一步是使用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

将这两种方法合并，就是本节所需要的解决方案。