将字符和数字数据分离
问题在一列中数字数据和字符数据混合存储在一起（真不幸），要将这些数据中的数字和字符分离出来。考虑下面列出的数据集：
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 (
8select ename||cast(sal as char(4)) data
9 from emp
10 ) x
Oracle
使用函数TRANSLATE和REPLACE来将数字与字符分离。
1select 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 (
10select 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
将这两种方法合并，就是本节所需要的解决方案。
页:
[1]