[Oracle] REGEXP_INSTR()

오라클 REGEXP_INSTR()

 

REGEXP_INSTR()

   -

 

REGEXP_INSTR(source_char, pattern)

REGEXP_INSTR(source_char, pattern, position, occurence, return_opt, match_prarm, subexpr)

의 형태

 

REGEXP_INSTR extends the functionality of the INSTR function by letting you search a string for a regular expression pattern. The function evaluates string using characters as defined by the input character set. It returne an integer indicating the beginning or ending position of the matched substring, depending of the value of the return_option argument. If no match is found, then the function returns 0.

 

This function compiles with the POSIX regular expression standard and the Unicode Regular Expression Guildelines. 

• sour_char is a character expression that serves as the search value. It is commonly a character column and can be of any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
• pattern is the regular expression. It is usually is a text literal and can be of any of the data types CHAR, VARCHAR2, NCHAR, or NVARCHAR2. It can contain up to 512 bytes. If the data type of pattern is different from the data type of source_char, then Oracle Database convers pattern to the data type of source_char, then Oracle Database converts pattern to the data type of source_char. 
• Position is a positive integer indicating the character of source_char where Oracle should begin the sarch. The default 1, meaning that Oracle begins the search at the first character of source_char.
• occurence is a positive integer indicating which occurence of pattern in source_char Oracle should search for. The default is 1, meaning that Oracle searches ofr the first occurence of pattern. If occurence is greater than 1, then the database searches for the second occurence beginning with the first character following the first occurence of pattern, and so forth. This behavior is different from the INSTR function, which begin its search for the second occurence at the second character of the first occurence.
• return_option lets you specify what Oracle should return in relation to the occurence :
  -   If you specify 0, then Oracle returns the position of the first character of the occurence. This is the default.
  -   If you specify 1, then Oracle returns the position of the character following the occurence. 
• match_parameter is a text literal that lets you change the default matching behavior of the function. The behavior of this parameter is the same for this function as for REGEXP_COUNT. 
• For a pattern with subexpression, subexpr is an integer form 0 to 9 indicating which subexpression in pattern is the target of the function. The subexpr is a fragment of pattern enclosed in parantheses. Subexpressions can be nested. Subexpressions are numbered in orde rin which their left parentheses appear in pattern. For example, consider the following expression:
  
  0123(((abc)(de)f)ghi)45(678)
  
  This expression has five subexpressions in the following order : "abcdefghi" followed by "abcdef", "abc", "de" and "678".
  
  If subexpr is zero, then the position of the entire substring that matches the pattern is returned. If subexpr is grater than zero, then the position of the substring gragment that correspongs to subexpression number subexpr in the matched substring is returned. If pattern does not have at least subexpr subexpression, the function returns zero. A null subexpr value returns NULL. The default value for subexpr is zero.

 

SQL > SELECT REGEXP_INSTR('500 Oraccle Parkway, Redwood Shores, CA', '[^ ]+', 1, 6) REGEXP_INSTR FROM DUAL ;

   --> 38