[转]把字母数字串转换为数值

http://www.blogjava.net/jxhkwhy/articles/200495.html

问题:对于字母数字的数据，只返回数字值。从字符串“paul123f321”中返回123321。

解决方案

DB2

使用函数TRANSLATE和REPLACE，从字母数字串中提取数字字符：

 select cast(

         replace(

       translate( 'paul123f321',

                  repeat('#',26),

                  'abcdefghijklmnopqrstuvwxyz'),'#','')

         as integer ) as num

    from t1

Oracle和PostgreSQL

使用函数TRANSLATE和REPLACE，可以从包含字母数字的字符串中提取数字字符：

 select cast(

         replace(

       translate( 'paul123f321',

                  'abcdefghijklmnopqrstuvwxyz',

                 rpad('#',26,'#')),'#','')

         as integer ) as num

   from t1 

  

MySQL和SQL Server

到本书编写时为止，这两个供应商都不支持TRANSLATE函数，因此这里不能给出解决方案了。

讨论

两种解决方案的唯一差别是语法，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去掉所有“#”。至此，仅剩下数字。

_______________________________________

考虑小数点的话 把 ‘0123456789’ 换成 ‘.0123456789 ’
不过潜在着小数点位置的问题、暂不考虑。