TRANSLATE

The UTL_RAW.TRANSLATE procedure performs a byte-by-byte translation on data of the RAW data type.

Syntax

UTL_RAW.TRANSLATE (
   r        IN RAW,    
   from_set IN RAW,    
   to_set   IN RAW  
) RETURN RAW;

Parameters

Considerations

Scans the value of the source byte string r in bytes:

• If this value is present in from_set, the corresponding byte in to_set is used.
• If the byte is not found in from_set, it is directly copied to the result.
• A byte in from_set is deleted if it does not have a corresponding item in to_set. If the from_set string is longer than the to_set string and some bytes in the from_set string do not have corresponding translations, the matching bytes are deleted.
• If to_set is longer than from_set, the extra bytes in to_set are ignored.
• If a byte is repeated in from_set, the duplicate bytes are ignored.
• An exception is raised if any of the three parameters are NULL or have a length of 0.
• If the length of the BYTES value is odd, a 0 is added to the left during processing.

Use case

Execute the following statement by using the UTL_RAW.TRANSLATE function to replace the specified character in the input byte sequence with F, and preserve the rest of the characters.

obclient> SELECT UTL_RAW.TRANSLATE('1236567812125612344434341234567890ABAA1234','12AA34','FFFFFF') FROM dual;

The query returns the result as follows:

+-----------------------------------------------------------------------------------+
| UTL_RAW.TRANSLATE('1236567812125612344434341234567890ABAA1234','12AA34','FFFFFF') |
+-----------------------------------------------------------------------------------+
| FF365678FFFF56FFFF44FFFFFFFF567890ABFFFFFF                                        |
+-----------------------------------------------------------------------------------+