Oracle character/String substitution

First, the grammar

Repalce (STR_SOURCE,STR1,STR2) replaces the STR1 string in Str_source with a str2 string, which is the same as the next when str2 is null or '

Replace (STR_SOURCE,STR1) to remove the str1 string in Str_source

Regexp_replace (STR_SOURCE,PATTERN_STR,REP_STR) supports regular expressions that are similar to replace but are more powerful

Regexp_replace (STR_SOURCE,PATTERN_STR) removes the PATTERN_STR string from the Str_source

Translate (STR_SOURCE,CHR1,CHR2) replaces chr1 characters in Str_source with CHR2 in characters. If the CHR1 is longer than the CHR2, the characters in Chr1 that are not in CHR2 are excluded because there are no corresponding substitution characters. Note that CHR2 cannot be null or ', otherwise the return value is empty

The Replace function replaces a value in a string with a different value. For example, you can use a matching number to replace each occurrence of the 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, it is deleted when the value of the search_string variable is found. Input can be--char, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB for any character data type.

--------------------------------------------------------------------------------------------------

2. The function of the REPLACE function is extended in several aspects by the Regexp_replace function. It supports the use of regular expressions in search patterns, as well as 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 case-insensitive.

The code is as follows	Copy Code
Regexp_replace (source_string, pattern [, Replace_string [, Position [, Occurrence [, Match_parameter] ] ] ] )

Except for replace_string, all of the variables here are already described in the previous chapters of this chapter. Replace_string tells Oracle what to use to replace the part in source_string that matches pattern. The occurrence variable is a non-negative integer that specifies the number of times the action is taken: if 0, all occurrences are substituted, and Oracle replaces the nth match if one is specified.

code is as follows

copy code

1.select Regexp_substr (phone,  2. ' ([[[:d Igit:]]{3}]-([[[[[:d Igit:]]{3}]-([[[[:d igit:]]{4}] '   3.] "Regexp_substr"   4.from address;  5.regexp_subst  6------------  7.213-555-0223  8.415-555-7530  Instance Sql> Select replace (' 4683,968,969 ', ', ', ' $ ') from Dual; REPLACE (' 4683,968,969 ', ', ', ' $ ' ------------------------------ 4683$968$969 Sql> Select To_ Number (' 520 ') from dual; To_number (' 520 ') ----------------  520

------------------------------------------------------------------------------------------------------------

3, a character substitution function translate, unlike the Replace function, the Translate function is a character-level substitution, rather than a string substitution.

The syntax is as follows:

TRANSLATE (expr, from_string, to_string)

To put it simply is to replace the characters in the from_string with the characters in the to_string, with examples as follows:

code is as follows

copy code

sql> Select translate (' 123456789 ', ' 456 ', ' abc ') from dual; TRANSLATE --------- 123abc789 Sql> Select TRANSLATE (' 123456789 ', ' 456 ', ' AB ') from dual; Translat -------- 123ab789 Sql> Select translate (' 123456789 ', ' 4564 ', ' a ') from dual; Translat ------- 123a789 Select translate (' abcc123a ', ' abc ', '-+= '), translate (' abcc123a ', ' abc ') , '-+ '), translate (' abcc123a ', ' #abc ', ' # ') from dual;  translate (' abcc123a ', ' abc ', '-+ TRANSLATE (' abcc123a ', ' abc ', '-+ TRANSLATE ', ' #ABC ', ' # ------- ----------------------- ------------------------------ ------------------------------

-+==123--+123-123
1, with the character '-', ' + ', ' = ' corresponds to replace ' a ', ' B ', ' C ' character;

2, ' abc ' length is 3, '-+ ' length is 2, the character ' C ' has no corresponding character substitution, therefore is removed;

3, remove the characters ' a ', ' B ', ' C ', translate has a special use of #, with # Beginning of the expression of all characters