The Oracle translate function and replace function Usage Details, oracletranslate

The Oracle translate function and replace function Usage Details, oracletranslate

Syntax of the translate function:

translate(expr, from_strimg, to_string)

Introduction:

Translate returns expr, where all occurrences of each character in from_string are replaced by the corresponding character in to_string. The characters in expr that are not in from_string are not replaced. If expr is a string, you must put it in single quotes. From_string can contain more characters than to_string. In this case, the extra characters at the end of from_string do not have corresponding characters in to_string. If these extra characters appear in the characters, they will be removed from the return value.

You cannot use the to_string null string to delete all characters in from_string from the returned value. Oracle Database interprets null strings as null. If this function has null parameters, null is returned.

Translate provides functions related to the replace function. Replace allows you to replace and delete strings with one string. Translate allows you to replace one-to-one with multiple single characters in an operation.

This function does not directly support CLOB data. However, CLOB can be passed as a parameter through implicit data conversion.

Example:

The following statement converts a sentence to a string separated by underscores. From_string contains four characters: pound signs, dollar signs, spaces, and asterisks. To_string only contains one @ symbol and two underscores. Therefore, the fourth character in from_string is not replaced. Therefore, the asterisk is deleted from the returned value.

SELECT TRANSLATE('itmyhome#163.com$is my* email', '#$ *', '@__') from dual----------itmyhome@163.com_is_my_email

Replace Function

Syntax:

REPLACE(char, search_string,replacement_string)

Usage:

Convert all the string search_string in char to the string replacement_string.

Example:

SQL> select REPLACE ('fgsgswsgs ', 'fk', 'J') return value from dual; Return Value --------- fgsgswsgs SQL> select REPLACE ('fgsgswsgs ', 'sg ', 'eeerrrttt') return value from dual; Return Value --------------------- fgeeerrrtttsweeerrrttts

Analysis:

In the first example, because 'fgsgswsgs 'does not have a string that matches 'fk,

The returned value is still 'fgsgswsgs ';

In the second example, convert all the strings 'sg 'in 'fgsgswsgs' to 'eeerrrttt '.

Conclusion: replace and translate are both substitution functions,

However, replace is for strings, while translate is for a single character.

Differences from replace functions

select translate('itmyhome#163%com', '#%', '@.') from dual;select replace('itmyhome#163%com', '#%', '@.') from dual;---------itmyhome@163.comitmyhome#163%com

In the above translate function, replace # With @, and %.

Replace does not achieve this effect because the # % overall combination is not found.

Summary

The above is a detailed explanation of the usage of the Oracle translate and replace functions. I hope to help you. If you have any questions, please leave a message, the editor will reply to you in a timely manner. Thank you very much for your support for the help House website!