Oracle character/string replacement

Replace the replce, regexp_replace, and translate functions with strings in ORACLE. I will introduce them to you.

Replace the replce, regexp_replace, and translate functions with strings in ORACLE. I will introduce them to you.

Replace the replce, regexp_replace, and translate functions with strings in ORACLE. I will introduce them to you.

I. Syntax

Repalce (str_source, str1, str2) replaces str1 string in str_source with str2 string. When str2 is null or '', it works the same as the next one.

Replace (str_source, str1) removes the str1 string from str_source.

Regexp_replace (str_source, pattern_str, rep_str) supports regular expressions, which are similar to replace, but more powerful.

Regexp_replace (str_source, pattern_str) removes the pattern_str string in str_source.

Translate (str_source, chr1, chr2) replace chr1 in str_source with chr2 in units of characters. If chr1 is longer than chr2, the characters in chr1 but not in chr2 will be removed because there is no corresponding replacement character. Note that chr2 cannot be null or ''; otherwise, the return value is empty.

The REPLACE function uses another value to REPLACE a value in the string. For example, you can use a matching number to replace each occurrence of a letter. The format of REPLACE is as follows:

1. REPLACE (char, search_string [, replace_string]) if the value of the replace_string variable is not specified, delete the value of the search_string variable. The input can be any character data type-CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

Bytes --------------------------------------------------------------------------------------------------

2. The REGEXP_REPLACE function extends the REPLACE function in several aspects. It supports regular expressions in the search mode, and also supports the variables described earlier in this chapter, namely position, occurrence, and match_parameter, so that you can choose to replace only some matching values, or not case sensitive.

The Code is as follows:
REGEXP_REPLACE (source_string, pattern [, Replace_string [, Position [, Occurrence [, Match_parameter] ] ] ] )

All variables except replace_string are described in the previous sections of this chapter. Replace_string tells Oracle what to use to replace the pattern matching part in source_string. The occurrence variable is a non-negative integer that specifies the number of operations. If it is 0, all matching items are replaced. If a positive number is specified, Oracle replaces the nth matching.

The Code is as follows:
1. select REGEXP_SUBSTR (Phone, 2. '([[: digit:] {3})-([[: digit:] {3})-([[: digit:] {4 })' 3.) "REGEXP_SUBSTR" 4. from ADDRESS; 5. REGEXP_SUBST 6 .------------ 7.213-555-0223 8.415-555-7530 Instance SQL> select replace ('123', '$') from dual; REPLACE ('20140901', '$' ------------------------------ 4683 $968 $969 SQL> select to_number ('123') from dual; TO_NUMBER ('123 ') ---------------- 520

Bytes ------------------------------------------------------------------------------------------------------------

3. A character replacement function translate. Unlike the replace function, the translate function replaces characters rather than strings.

The syntax is as follows:

TRANSLATE (expr, from_string, to_string)

To put it simply, replace the characters in from_string with the characters in to_string for expr content. The example is as follows:

The Code is as follows:

SQL> select translate ('20160301', '20160301', 'abc') from dual; TRANSLATE --------- 123abc789 SQL> select translate ('20160301', '20160301', 'AB') from dual; TRANSLAT -------- 123ab789 SQL> select translate ('20160301', '20160301', 'A') from dual; TRANSLAT ------- 123a789 Select translate ('abcc123a ', 'abc','-+ = '), translate ('abcc123a', 'abc', '-+'), translate ('abcc123a ', '# abc',' # ') from dual; TRANSLATE ('abcc123a ', 'abc','-+ TRANSLATE ('abcc123a ', 'abc','-+ TRANSLATE ('abcc123a ',' # abc ','# ------------------------------------------------------------------------------------------

-+ = 123--+ 123-123
1. Replace the 'A', 'B', and 'C' characters with '-', '+', and '=;

2. The length of 'abc' is 3, the length of '-+' is 2, and the character 'C' has no corresponding characters to replace, so it is removed;

3. Remove characters 'A', 'B', and 'c'. translate has the special usage of #, which indicates all characters starting #.