License -------- 9XXX999 你那个例子: select translate('111225dfadfas#3234234','#0123456789','3') from dual; '#'变成3,'01..9'变成空(NULL)
select translate('111225dfadfas#3234234','#0123456789','3') from dual; 就是把'111225dfadfas#3234234'中所有的#号变换成3,其它的换成空 select translate('111225dfadfas#3234234','#0123456789','3123') from dual; 就是把'111225dfadfas#3234234'中所有的#号变换成3,将0换成1,1 换成2,2换成3,3456789 换成空 结果为22233dfadfas333
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;
'ABC...Z' 都变成 'X' , '012 . . . 9' 都变成 '9':SELECT TRANSLATE('2KRW229',
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') "License"
FROM DUAL;
License
--------
9XXX999 你那个例子:
select translate('111225dfadfas#3234234','#0123456789','3') from dual;
'#'变成3,'01..9'变成空(NULL)
就是把'111225dfadfas#3234234'中所有的#号变换成3,其它的换成空
select translate('111225dfadfas#3234234','#0123456789','3123') from dual;
就是把'111225dfadfas#3234234'中所有的#号变换成3,将0换成1,1 换成2,2换成3,3456789
换成空
结果为22233dfadfas333
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
简单的说translate就是把源字符串中的源字符用目标字符去替换得到目标字符串的函数。
注意translate是按位替换的。