Replace and translate of Oracle replacement function

1. Replace function

The replace function is used to replace the specified string in the source target with the corresponding characters, as an example:

(1) replace the ji in the "jisuanji" string with 1

SQL > select replace ('jisuanji','ji',1) from dual

REPLACE ('JISUANJI','JI',1)

-

1suan1

Description: first look for the string 'ji'' in the 'jisuanji' string. If you find it, replace it with 1, otherwise leave the output as is.

(2) replace the js in the "jisuanji" string with 1

SQL > select replace ('jisuanji','js',1) from dual

REPLACE ('JISUANJI','JS',1)

-

Jisuanji

Note: the continuous character 'js'' was not found in the string 'jisuanji'', so leave the output as is

2. Translate function

(3) the main difference between the translate function and the replace function is that translate splits the specified string into characters to match and each character corresponds to the replacement character one by one, as an example:

Replace ji in the "jisuanji" string with 1

SQL > select translate ('jisuanji','ji',1) from dual

TRANSLATE ('JISUANJI','JI',1)

-

1suan1

Explanation: in the above example, sql splits' ji' into two characters, and then j matches with 1, but I does not match the replacement character, so it is empty, so the effect is erased, and the final effect is 1suan1, which is the same as the same example of replace, but the principle is not the same, which needs to be noted.

SQL > select translate ('jisuanji','js',12) from dual

TRANSLATE ('JISUANJI','JS',12)

-

1i2uan1i

The above example reveals the principle of the translate function.

Summary: through the comparative analysis of replace and translate functions and combined with personal experience, it is considered that the operation of translate function is flexible, and it is recommended to use translate function instead of replace function.