PeopleSoft Query Oracle Functions

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