What are the replacement functions in Oracle?

Oracle’s replacement functions are: 1. translate() function, syntax “TRANSLATE(char, from, to)”; 2. replace() function, syntax “REPLACE(string, search value, replacement value) )".

The operating environment of this tutorial: Windows 7 system, Oracle 11g version, Dell G3 computer.

Replace function replace and translate function in oracle

##1.translate

Syntax:

TRANSLATE(char, from, to)

Usage: Returns the string after replacing each character appearing in from with the corresponding character in to.

If the from string is longer than the to string, the extra characters in from than in to will be deleted.

If one of the three parameters is empty, the return value will also be empty.

Example:

SQL> select translate('abcdefga','abc','wo') 返回值 from dual;

Return value

 wodefgw

Analysis:

This statement converts 'abc' in 'abcdefga' to 'wo', Since 'a' in 'abc' corresponds to 'w' in 'wo', all 'a's in 'abcdefga' are converted to 'w';

and 'b' in 'abc' corresponds to 'o' in 'wo', so all 'b's in 'abcdefga' are converted to 'o';

The 'c' in 'abc' has no corresponding counterpart in 'wo' characters, so delete all 'c' in 'abcdefga';

Simply put, it is to convert the characters in from to the characters corresponding to their positions in to. If the characters cannot be found in to The corresponding character will be deleted from the return value.

In actual business, it can be used to delete some abnormal data. For example, a field t_no in table a represents a phone number, and the phone number itself should be a string composed of numbers. In order to delete those containing For non-numeric abnormal data, the translate function is used:

SQL> delete from a，
          where length(translate(trim(a.t_no),
                   '0123456789' || a.t_no,
                             &#39;0123456789&#39;)) <> length(trim(a.t_no));

2.replace

Syntax:

REPLACE(char, search_string,replacement_string)

Usage: Convert all string search_string in char to string replacement_string.

Example 1:

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

Return value

fgsgswsgs

Example 2:

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

Return value

fgeeerrrtttsweeerrrttts

Analysis:

• In the first example, since there is no string matching 'fk' in 'fgsgswsgs', the return value is still 'fgsgswsgs';

• In the two examples, all the strings 'sg' in 'fgsgswsgs' are converted to 'eeerrrttt'.

Summary: To sum up, replace and translate are both replacement functions, except that replace targets a string, while translate targets a single character.

Recommended tutorial: "

Oracle Tutorial"

The above is the detailed content of What are the replacement functions in Oracle?. For more information, please follow other related articles on the PHP Chinese website!