Oracle Replace () function usage

Introduction to Usage

REPLACE (char, search_string [, replacement_string])

Char: String to wait for replacement

Search_string: Search for strings to replace

Replacement_string: Replacement string

If replacement_string is either default or null, then all occurrences of search_string in char will be removed

If search_string is null, then the result is Char

The code is as follows	Copy Code
Select REPLACE (' JACK and Jue ', ' J ', ' BL ') "Changes" from DUAL;

Example 1

The format of ' 2011-10-11 ' in the database, the result imported data for ' 2011/10/11 '

The code is as follows	Copy Code
Update table 1 T. column 1=replace (select column 1from table 1 A where a. primary key column =t. Primary key column), '/', '-' solves our problem.

Replace string-level substitution

Such as:

The code is as follows	Copy Code
SELECT REPLACE (' ACCD ', ' CD ', ' EF ') from dual; --> AEFD

Translate character-level substitution

Such as:

The code is as follows	Copy Code
Select Translate (' ACDD ', ' CD ', ' EF ') from dual; -->aeff

Separate detailed

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, then all search_string are removed

The code is as follows	Copy Code
Select replace (' ACDD ', ' CD ', ') from dual; --> AD

If search_string is null, then return the original Char

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

(is also a case of both empty)