|
translate(OriginalText, MaskCharacters, ReplacementCharacters)
translate alters OriginalText by switching each character in MaskCharacters by the corresponding character ReplacementCharacters.
If ReplacementCharacters is shorter than MaskCharacters, then the remaining characters are simply removed from OriginalText.
Examples
Function | Result |
translate('soon','o','e') | 'seen' |
translate('soon','ns','mb') | 'boom' |
translate('abcdefgh','bdf','xx') | 'axcxegh' |
translate('1234567890','-1234567890','-') | '' |
Some useful translate tricks:
Function | Result |
translate(NATIONAL_ID,
'0123456789'
||'abcdefghijklmnopqrstuvwxyz'
||'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
,rpad('9',10)
||rpad('x',26)
||rpad('X',26)) |
Shows the format of NATIONAL_ID - useful in a group by query |
translate(TextField,'-0123456789','-') | Removes digits from text field. |
PeopleSoft Query: Hints and Tips
|