Oracle Regular Expression

[Guide] Using regular expressions in Oracle requires the following four functions:

Oracle uses regular expressions without these four functions:

1. Regexp_like

2. Regexp_substr

-- Select regexp_substr ('aaa, BB, CCCCC, DDD, vvv ',' [^,] + ', 1, 4) from dual

3. Regexp_instr

4. Regexp_replace

 

Parameters

1. Source_char: the input string, which can be a column name, A String constant, or a variable.

2. Pattern, a regular expression.

3. Match_parameter, matching option.

Value Range: I (Case Insensitive), C (case sensitive), and N (point number. do not match line feed characters; M: multi-line mode; X: Extended Mode. Ignore the blank characters in the regular expression.

4. Position, which indicates the regular expression matching starting from the nth character.

5. Occurrence, which identifies the matching groups.

6. Replace_string, the replacement string.

 

Example:

Select STR from TMP where id = 'like' and regexp_like (STR, 'a \ D + ',' I '); --' I 'case insensitive

Regexp_substr (STR, '[^,] + ')

Regexp_instr ('192. 168.0.1 ',' \ d', 1, level) IND -- position of each number

Regexp_replace (STR, '020', 'gz ') STR,