SQL 将含有字母和数字的字符串转换为数字|极客教程

SQL 将含有字母和数字的字符串转换为数字，你有字母和数字混合的数据，并希望提取出其中的数字部分。例如，对于字符串 paul123f321，你想得到的结果是数字 123321。

SQL 将含有字母和数字的字符串转换为数字问题描述

你有字母和数字混合的数据，并希望提取出其中的数字部分。例如，对于字符串 paul123f321，你想得到的结果是数字 123321。

SQL 将含有字母和数字的字符串转换为数字解决方案

DB2
使用函数 TRANSLATE 和 REPLACE 从含有字母和数字的字符串里提取出数字字符。

1 select cast(
2        replace(
3       translate( 'paul123f321',
4                  repeat('#',26),
5                  'abcdefghijklmnopqrstuvwxyz'),'#','')
6         as integer ) as num
7   from t1

Oracle 和 PostgreSQL
使用函数 TRANSLATE 和 REPLACE 从含有字母和数字的字符串里提取出数字字符。

1 select cast(
2        replace(
3       translate( 'paul123f321',
4                 'abcdefghijklmnopqrstuvwxyz',
5                 rpad('#',26,'#')),'#','')
6        as integer ) as num
7   from t1

MySQL 和 SQL Server
在写作本书时，这两个数据库尚未支持 TRANSLATE 函数，因而无法提供解决方案。

SQL 将含有字母和数字的字符串转换为数字扩展知识

两种解决方案的唯一区别是语法：DB2 使用的是 REPEAT 函数而非 RPAD 函数，并且 TRANSLATE 函数的参数列表顺序也有所不同。下面的解释内容以 Oracle 和 PostgreSQL 解决方案为准，也会适当兼顾 DB2 的解决方案。如果我们试着从内到外执行查询（从 TRANSLATE 开始），就会明白整个查询其实不难。首先，TRANSLATE 函数把每个非数字字符替换为 #。

select translate( 'paul123f321',
                  'abcdefghijklmnopqrstuvwxyz',
                  rpad('#',26,'#')) as num
  from t1
　
NUM
-----------
####123#321

接着，使用 REPLACE 函数删除 #，最后再把剩余的部分转换为数值类型即可。这个例子非常简单，因为要处理的数据是一个只包含字母和数字的字符串。如果该字符串还包含其他字符，那么从反面着手可能会更容易：不是先找到非数字字符并删除它们，而是先找到数字字符再去掉其他字符。下面的例子展示了这种方法。

select replace(
       translate('paul123f321',
       replace(translate( 'paul123f321',
                          '0123456789',
                          rpad('#',10,'#')),'#',''),
               rpad('#',length('paul123f321'),'#')),'#','') as num
  from t1
　
NUM
------
123321

上述解决方案看起来比最初的做法更令人费解，不过如果拆开来看，也不是很难懂。我们来观察最内层的 TRANSLATE 函数调用。

select translate( 'paul123f321',
                  '0123456789',
                  rpad('#',10,'#'))
  from t1
　
TRANSLATE('
-----------
paul###f###

第一步就不同：它没有用 # 替换非数字字符，反而用 # 逐个替换全部数字字符。下一步删除 #，这样就只剩下非数字字符了。

select replace(translate( 'paul123f321',
                          '0123456789',
                          rpad('#',10,'#')),'#','')
  from t1
　
REPLA
-----
paulf

然后再次调用 TRANSLATE 函数，用 # 替换原字符串里的所有非数字字符（上一步的返回结果）。

select translate('paul123f321',
       replace(translate( 'paul123f321',
                          '0123456789',
                          rpad('#',10,'#')),'#',''),
               rpad('#',length('paul123f321'),'#'))
  from t1
　
TRANSLATE('
-----------
####123#321

这时我们不妨停下来，仔细观察最外层的 TRANSLATE 函数调用。RPAD 函数的第二个参数（对于 DB2 而言，是 REPEAT 函数的第二个参数）是原字符串的长度。这是一种巧妙的用法，因为一个字符串里任何字符的出现次数都不会超过整个字符串的长度。现在，全部非数字字符都被替换成了 #。最后，调用 REPLACE 函数去掉这些 #，因此最终就只剩下数字。