Replace and translate of Oracle replacement functions

Source: Internet
Author: User
Tags translate function

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.