java英文字母转换为数值_SQL语句-把字母数字串转换为数值

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

解决方案

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函数，因此这里不能给出解决方案了。

讨论

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