极客教程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
最后，将上述两个方法结合起来就是本问题的解决方案。