Shulou(Shulou.com)06/01 Report--

In this issue, the editor will bring you about the meaning of the translate function and replace function in Oracle. The article is rich in content and analyzes and describes it from a professional point of view. I hope you can get something after reading this article.

Translate function syntax:

Translate (expr, from_strimg, to_string)

Brief introduction:

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

You cannot use the empty string of to_string to remove all characters in from_string from the return value. The Oracle database interprets the empty string as empty and returns null if the function has an empty argument.

Translate provides functionality related to the replace function. Replace lets you replace one string with another, as well as delete strings. Translate allows you to replace multiple single characters, one to one, in a single 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 an underscore. From_string contains four characters: pound sign, dollar sign, space, and asterisk. To_string contains only an @ symbol and two underscores. This leaves the fourth character in from_string without a corresponding replacement, so the asterisk is removed 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:

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

For 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 there is no string matching 'fk'' in 'fgsgswsgs'

So the return value is still 'fgsgswsgs'

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

Summary: to sum up, replace and translate are both substitution functions

It's just that replace is for strings, while translate is for individual characters.

The difference between and replace function

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

The above translate function replaces # with @ and% with.

However, replace did not achieve this effect because the overall combination of #% was not found.

The above is the meaning of the translate function and replace function in Oracle shared by Xiaobian. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.