[每日一题] OCP1z0-047 :2013-08-01正则表达式--- REGEXP_REPLACE函数(二)

pattern is the regular expression. It is usually a text literal and can be of any of the datatypesCHAR, VARCHAR2, NCHAR, or NVARCHAR2. It can contain up to 512 bytes. If the datatype ofpattern is different from the datatype of source_char, Oracle Database convertspattern to the datatype of source_char. For a listing of the operators you can specify inpattern, please refer to Appendix C, "Oracle Regular Expression Support".

replace_string can be of any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, orNCLOB. If replace_string is a CLOB or NCLOB, then Oracle truncates replace_string to 32K. The replace_string can contain up to 500 backreferences to subexpressions in the form\n, where n is a number from 1 to 9. If n is the backslash character inreplace_string, then you must precede it with the escape character (\\). For more information on backreference expressions, please refer to the notes to"Oracle Regular Expression Support", Table C-1.

position is a positive integer indicating the character of source_char where Oracle should begin the search. The default is 1, meaning that Oracle begins the search at the first character ofsource_char.

occurrence is a nonnegative integer indicating the occurrence of the replace operation:

If you specify 0, then Oracle replaces all occurrences of the match.

If you specify a positive integer n, then Oracle replaces the nth occurrence.

match_parameter is a text literal that lets you change the default matching behavior of the function. This argument affects only the matching process and has no effect onreplace_string. You can specify one or more of the following values formatch_parameter:

'i' specifies case-insensitive matching.

'c' specifies case-sensitive matching.

'n' allows the period (.), which is the match-any-character character, to match the newline character. If you omit this parameter, the period does not match the newline character.

'm' treats the source string as multiple lines. Oracle interprets^ and $ as the start and end, respectively, of any line anywhere in the source string, rather than only at the start or end of the entire source string. If you omit this parameter, Oracle treats the source string as a single line.

'x' ignores whitespace characters. By default, whitespace characters match themselves.

If you specify multiple contradictory values, Oracle uses the last value. For example, if you specify'ic', then Oracle uses case-sensitive matching. If you specify a character other than those shown above, then Oracle returns an error.

If you omit match_parameter, then:

The default case sensitivity is determined by the value of the NLS_SORT parameter.

A period (.) does not match the newline character.

The source string is treated as a single line.

Examples

The following example examines phone_number, looking for the patternxxx.xxx.xxxx. Oracle reformats this pattern with (xxx)xxx-xxxx.

[html] 
SELECT  
  REGEXP_REPLACE(phone_number,  
                 '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})',  
                 '(\1) \2-\3') "REGEXP_REPLACE"  
  FROM employees;  
  
REGEXP_REPLACE  
--------------------------------------------------------------------------------  
(515) 123-4567  
(515) 123-4568  
(515) 123-4569  
(590) 423-4567  
. . .  

The following example examines country_name. Oracle puts a space after each non-null character in the string.

[html] 
SELECT  
  REGE