Oracle replace () function usage

In the oracle database, we will use the replace function to replace strings. The following describes how to use replace () in oracle. For more information, see.

Usage

REPLACE (char, search_string [, replacement_string])

Char: string to be replaced

Search_string: Search for the string to be replaced

Replacement_string: replace string

If replacement_string is null by default, all search_string characters in char will be removed.

If search_string is null, the result is char.

The Code is as follows:	Copy code
Select replace ('Jack and JUE ', 'J', 'bl') "Changes" from dual;

Example 1

In the format of '2017-10-11 'in the database, the imported data is '2017/11'

The Code is as follows:	Copy code
Update Table 1 t set t. column 1 = replace (select column 1 from table 1 a where. primary Key column = t. primary Key column), '/', '-') solves our problem.

Replace string-level replacement

For example:

The Code is as follows:	Copy code
Select replace ('accd', 'cd', 'ef ') from dual; --> aefd

Replacement of the translate character level

For example:

The Code is as follows:	Copy code
Select translate ('acdd', 'cd', 'ef ') from dual; --> aeff

 

Detailed explanation

Replace:

Syntax: REPLACE (char, search_string [, replacement_string])

Explanation: In replace, each search_string is replaced by replacement_string.

The Code is as follows:	Copy code
Select replace ('acdd', 'cd', 'ef ') from dual; --> aefd

If replacement_string is null or null, all search_strings are removed.

The Code is as follows:	Copy code
Select replace ('acdd', 'cd', '') from dual; --> ad

If search_string is null, the original char is returned.

The Code is as follows:	Copy code
Select replace ('acdd', 'ef ') from dual; --> acdd Elect replace ('acdd', '','') from dual; --> acdd

(This is also the case where both are empty)