Explanation of the use of Translate in Oracle and DB2 databases, db2translate

Explanation of the use of Translate in Oracle and DB2 databases, db2translate

I. Oracle Syntax:

Translate (string, from_str, to_str)

Eg: select translate ('abcdef', 'abf', 'cde') from dual;

Result: Move 'abf' in the 'abcdef' string to 'cde ')

2. Syntax in DB2:

Translate (string, to_str, from_str)

Eg: select translate ('abcdef', 'abf', 'cde') from sysibm. sysdummy1;

Result: ababff (replace 'cde' in the 'abcdef' string with 'abf ')

String: string to be processed

From_str: string character to be converted

To_str: character to be converted

(Note: In Oracle and DB2, the positions of the from_str and to_str parameters in the Translate method are exactly the opposite)

The Translate function searches for characters in from_str in string and replaces them with the characters in to_str (single character replacement ).

Iii. Use Cases of the Translate function (taking DB2 as an example ):

1. Verify a field (mobile phone number, zip code, date ...) Whether to include characters except numbers:

Eg: select trim (translate ('172.16345489asx', '', '123') from sysibm. sysdummy1;

(Trim is used to remove spaces)

Result: asx

2. Convert the number of a field to 9 and the letter to X:

Eg: select translate ('xgz201601 ', '9... X... ', '0123456789abcdef... ') From sysibm. sysdummy1;

(The intermediate parameters are 10 9 and 26 X, followed by 0-9 and A-Z)

Result: XXX999999

3. Extract letters or numbers from a string:

Select translate ('xgz201601 ', '', 'abcdef... ') From sysibm. sysdummy1;

Result: 201601

4. Conclusion:

1. If the character to be converted (from_str) does not exist in the character to be converted (to_str), the conversion is truncated:

Eg: select translate ('abcde', '12', 'bcde') from sysibm. sysdummy1;

Result: a12

2. The destination string (to_str) to be converted in oracle cannot be '', because'' is considered as a null value in oracle, so it cannot be matched and is returned as a null value. However, matching can be performed in DB2:

Eg :( oracle) select translate ('abcde', 'abc', '') from dual;

Display result:

Eg :( DB2) select translate ('abcde', '', 'abc') from sysibm. sysdummy1;

Display result: de