Detailed explanation of the translate function and replace function in Oracle

translate returns expr, in which all occurrences of each character in from_string are replaced by the corresponding characters in to_string, and the replace function converts all the string search_string in char into the string replacement_string. This article will share with you the translate in Oracle Let’s take a look at the usage of function and replace function. I hope it can help everyone.

translate function syntax:

translate(expr, from_strimg, to_string)

Introduction:

translate returns expr in which all occurrences of each character in from_string are replaced by the corresponding character in to_string. Characters in expr that are not in from_string will not be replaced. If expr is a string, then you must put it in single quotes. The arguments to from_string can contain more characters than to_string. In this case, the extra characters at the end of from_string have no corresponding characters in to_string. If these extra characters appear in 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. Oracle Database interprets empty strings as null, and if this function has empty parameters, null is returned.

translate provides functions related to the replace function. replace lets you replace one string with another, as well as delete strings. translate allows you to make multiple single-character, one-to-one replacements in one operation.

This function does not directly support CLOB data. However, CLOBs can be passed as parameters with implicit data conversion.

Example:

The following statement converts a sentence into an underscore-delimited string. from_string contains four characters: pound sign, dollar sign, space, asterisk. to_string contains only one @ 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:

Convert all string search_string in char to string replacement_string .

Example:

SQL> select REPLACE('fgsgswsgs', 'fk' ,'j') 返回值 from dual;
      返回值
      ---------
      fgsgswsgs
      SQL> select REPLACE('fgsgswsgs', 'sg' ,'eeerrrttt') 返回值 from dual;
      返回值
      -----------------------
      fgeeerrrtttsweeerrrttts

##Analysis:

First one In the example, since there is no string matching 'fk' in 'fgsgswsgs',

           so the return value is still 'fgsgswsgs';

                In the second example, the characters in 'fgsgswsgs' The string 'sg' is all converted to 'eeerrrttt'.

Summary: To sum up, replace and translate are both replacement functions,

It’s just that replace targets a string, while translate targets a single character.

The difference between the replace function

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

The above translate function replaces # is @, % is replaced by .

but replace does not achieve this effect because the overall combination of #% is not found

Related recommendations:

OracleBasic Learning Son Query

Oracle—Usage of distinct

A brief analysis of the application of table functions in Oracle

The above is the detailed content of Detailed explanation of the translate function and replace function in Oracle. For more information, please follow other related articles on the PHP Chinese website!