Oraclereplace () 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.

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.

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:
Select replace ('Jack and JUE ', 'J', 'bl') "Changes" from dual;

Example 1

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

The Code is as follows:
Update Table 1 t set t. column 1 = replace (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:
Select replace ('accd', 'cd', 'ef ') from dual; --> aefd

Replacement of the translate character level

For example:

The Code is as follows:
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:
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:
Select replace ('acdd', 'cd', '') from dual; --> ad

If search_string is null, the original char is returned.

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

(This is also the case where both are empty)