[ORACLE] REGEXP_COUNT()

오라클 REGEXP_COUNT()

 

REGEXP_COUNT()

   -

 

REGEXP_COUNT(source_char, pattern)

REGEXP_COUNT(source_char, pattern, position, match_param)

의 형태

 

REGEXP_COUNT complements the functionality of the REGEXP_INSTR function by returning the number of times a pattern occurs in a source string.  The function evaluates strings using characters as defined by the input character set. It retruns and integer indicating the number of occurences of pattern. If no match is found, then the function returns 0.

• source_char is a character expression that serves as the search value. It is commonly a character column and can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
• pattern is the regular expression. It is the usually a text literal and can be any of the data types CHAR, VARCHAR2, NCHAR, or CLOB, or NCLOB.
• REGEXP_COUNT ignores subexpression parentheses in pattern, For exampre, the pattern '123(45))' is equivalent to '12345'.
• 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 of source_char. After finding the first occurence of pattern, the database searches for a second occurence beginning with the first character following the first occurence.
• match_params is a text literal that lets you change the default matching behavior of the function. You can specify one or more of the following values for match_param :
  
  -    '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, then the period does not match the newline character.
  -   'm' treats the source string as multiple lines. Oracle interprets the caret(^) and dollar sign($) 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, then 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, then 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_params, then : 
  
  -   The default case sensitivity is determined by the value of the NLS_SORT paramter.
  -   A period(.) does not match the newline character.
  -   The source string is treated as a single line.

 

 

 

SQL> SELECT REGEXP_COUNT('123123123123123', '(12)3', 1, 'i') REGEXP_COUNT FROM DUAL ;

   --> 5

 

SQL> SELECT REGEXP_COUNT('123123123123123', '(12)3') REGEXP_COUNT FROM DUAL ;

   -->