Shulou(Shulou.com)05/31 Report--

This article introduces you how to use translate and replace functions in Oracle, the content is very detailed, interested friends can refer to, hope to be helpful to you.

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.

On how to use translate and replace functions in Oracle to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.