How to replace string in oracle?

Oracle method of replacing strings: 1. replce method, code is [REPLACE(sourceStr, searchStr, replacedStr)]; 2. translate method, code is [TRANSLATE('sourceStr')].

oracle method of replacing strings:

1. replce method

Usage 1:

REPLACE(sourceStr, searchStr, replacedStr)

sourceStr identifies the field name or string to be replaced, searchStr indicates the string to be replaced, replacedStr represents the string to be replaced.

Usage 2:

REPLACE(sourceStr, searchStr)

sourceStr identifies the field name or string to be replaced, searchStr indicates the string to be removed.

2. regexp_replace method

Usage 1:

regexp_replace(sourceStr, patternStr, replacedStr)

sourceStr identifies the field name or string to be replaced, patternStr represents the string matched by the regular expression to be replaced, and replacedStr represents the string to be replaced.

Usage 2:

regexp_replace(sourceStr, patternStr)

sourceStr identifies the field name or string to be replaced, patternStr represents the regular expression to be eliminated The matching string.

3. Translate method

Usage:

TRANSLATE('sourceStr', 'fromStr', 'toStr')

Replace the fromStr characters in sourceStr with toStr in character units. If fromStr is longer than toStr, characters in fromStr but not in toStr will be eliminated because there are no corresponding replacement characters. If one of the three parameters is empty, the return value will also be empty.

Let us summarize, the functions of regexp_replace and replace are very similar, but regexp_replace is more powerful, adds regular matching, is more flexible to use, and is suitable for more complex string replacement situations. Under normal circumstances, the replace function can already meet most string replacement needs. The difference between replace and translate is that replace targets a string and translate targets a single character.

Related learning recommendations: oracle database learning tutorial

The above is the detailed content of How to replace string in oracle?. For more information, please follow other related articles on the PHP Chinese website!