ORACLE中translate的妙用
发布时间：2010/8/30 16:07:17 来源：城市学习网 编辑：ziteng
下面是文档中的话
The following statement translates a license number. All letters 'ABC...Z' are translated to 'X' and all digits '012 . . . 9' are translated to '9':
SELECT TRANSLATE('2KRW229',
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') "License"
FROM DUAL;
License
--------
9XXX999
The following statement returns a license number with the characters removed and the digits remaining:
SELECT TRANSLATE('2KRW229',
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789')
"Translate example"
FROM DUAL;
Translate example
-----------------
2229
我们在利用TRANSLATE取出一个字段中的数字或文字的时候就可以巧用这个函数
select translate('23456中国3-00=.,45','0123456789'||'23456中国3-00=.,45','0123456789') from dual;
如果要取出汉字的话转变一下就可以了
select trim(translate('23456中国3-00=.,45',
'0123456789-=.,',
' '))
from dual;
