問題:對于字母數(shù)字的數(shù)據(jù)，只返回數(shù)字值。從字符串“paul123f321”中返回123321。
解決方案
DB2
使用函數(shù)TRANSLATE和REPLACE，從字母數(shù)字串中提取數(shù)字字符：
1 select cast(
2 replace(
3 translate( 'paul123f321',
4 repeat('#',26),
5 'abcdefghijklmnopqrstuvwxyz'),'#','')
6 as integer ) as num
7 from t1
Oracle和PostgreSQL
使用函數(shù)TRANSLATE和REPLACE，可以從包含字母數(shù)字的字符串中提取數(shù)字字符：
1 select cast(
2 replace(
3 translate( 'paul123f321',
4 'abcdefghijklmnopqrstuvwxyz',
5 rpad('#',26,'#')),'#','')
6 as integer ) as num
7 from t1
MySQL和SQL Server
到本書編寫時為止，這兩個供應(yīng)商都不支持TRANSLATE函數(shù)，因此這里不能給出解決方案了。
討論
兩種解決方案的唯一差別是語法，DB2使用函數(shù)REPEAT代替RPAD，而且TRANSLATE參數(shù)列表的順序也不同。以下的解釋采用了Oracle/PostgreSQL解決方案， DB2也類似。如果從里向外運行該查詢（僅僅從TRANSLATE開始），就會發(fā)現(xiàn)這非常簡單。首先，TRANSLATE把非數(shù)字字符轉(zhuǎn)換為“#”：
select translate( 'paul123f321',
'abcdefghijklmnopqrstuvwxyz',
rpad('#',26,'#')) as num
from t1
NUM
-----------
####123#321
由于現(xiàn)在所有非數(shù)字字符都用“#”表示了，因此只需使用REPLACE去掉它們，然后把結(jié)果轉(zhuǎn)換為數(shù)值。這個特殊的例子尤其簡單，因為字符串中只有字母和數(shù)字。如果還有其他字符，那么用另一種方法會更容易：不是找出非數(shù)字字符并去掉它們，而是找出所有數(shù)字字符，并去掉不屬于這些字符范圍的其他字符。下面的例子會有助于理解這種技巧：
select replace(
translate('paul123f321',
replace(translate( 'paul123f321',
'0123456789',
rpad('#',10,'#')),'#',''),
rpad('#',length('paul123f321'),'#')),'#','') as num
from t1
NUM
------
123321
較之原始方案，該解決方案看起來有點兒費解，但如果把它分解開來就容易理解了。觀察一下最內(nèi)層的TRANSLATE調(diào)用：
select translate( 'paul123f321',
'0123456789',
rpad('#',10,'#'))
from t1
TRANSLATE('
-----------
paul###f###
與原來方案不同的是，它沒有用“#”字符替換每個非數(shù)字字符，而是用“#”字符替換所有數(shù)字字符。接下來，去掉所有“#”，這樣，只剩下非數(shù)字字符：
select replace(translate( 'paul123f321',
'0123456789',
rpad('#',10,'#')),'#','')
from t1
REPLA
-----
paulf
下一步，再次調(diào)用TRANSLATE，這次用“#”字符替換原始字符串中的所有非數(shù)字字符（前面查詢的結(jié)果）：
select translate('paul123f321',
replace(translate( 'paul123f321',
'0123456789',
rpad('#',10,'#')),'#',''),
rpad('#',length('paul123f321'),'#'))
from t1
TRANSLATE('
-----------
####123#321
到這里停一停，檢驗一下最外層的TRANSLATE調(diào)用。RPAD的第二個參數(shù)（DB2中REPEAT的第二個參數(shù)）是原始字符串的長度。這樣做很方便，因為是沒有任何字符出現(xiàn)的次數(shù)會比它所在的整個字符串長。現(xiàn)在，用“#”字符替換所有非數(shù)字字符；最后一步，使用REPLACE去掉所有“#”。至此，僅剩下數(shù)字。