Replace and translate of Oracle replacement functions

One, replace function

The function of the Replace function is to replace the specified string in the source target with the corresponding character, for 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 ' Ji ' string in ' Jisuanji ' string, if found, replace with 1, otherwise remain as-is output.

(2) replace JS in "Jisuanji" string with 1;

sql> Select replace (' Jisuanji ', ' JS ', 1) from dual;

REPLACE (' Jisuanji ', ' JS ', 1)

--------------------------

Jisuanji

Description: ' JS ' is not found in the string ' Jisuanji ', so keep the output as it is;

Second, translate function

(3) The difference between the translate function and the Replace function is primarily that translate splits the specified string into characters to match and each character corresponds to the replacement character one by one, for example, as follows:

replace the JI in the Jisuanji "string with 1;

    

Sql> Select Translate (' Jisuanji ', ' Ji ', 1) from dual;

TRANSLATE (' Jisuanji ', ' JI ', 1)

----------------------------

1suan1

Description: In the example above, SQL splits ' Ji ' into ' j ' I ' These two characters, and then J and 1 to match, but I did not match to the substitution character, so is empty, the effect is erase, the result is 1suan1, which is the same as replace the same example effect, but the principle is different, this needs to be noted.

Sql> Select Translate (' Jisuanji ', ' js ', ' n ') from dual;

TRANSLATE (' Jisuanji ', ' JS ', 12)

-----------------------------

1i2uan1i

The above example shows the principle of the translate function.

Summary: By comparing the replace and translate functions with the personal experience, it is considered that the translate function is flexible and it is recommended to use the Translate function instead of the Replace function.

Replace and translate of Oracle replacement functions