TRANSLATE
Syntax
参见附图
Purpose
TRANSLATE returns char with all occurrences of each character in from replaced by
its corresponding character in to. Characters in char that are not in from are not
replaced. The argument from can contain more characters than to. In this case, the
extra characters at the end of from have no corresponding characters in to. If these
extra characters appear in char, they are removed from the return value. You cannot
use an empty string for to to remove all characters in from from the return value.
Oracle interprets the empty string as null, and if this function has a null argument,
it returns null.
Example 1
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
Example 2
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
del.jpg
(10.31 KB, 下载次数: 49)
-10-14 13:15 上传
如果觉得《oracle 去除英文字母 请教：如何去掉字段中的所有标点符号（包括全角的和半角的）...》对你有帮助，请点赞、收藏，并留下你的观点哦！
