Code Jutsu

What is regular expression ? A regular expression is a pattern describing a certain amount of text.
You can use the oracle version 9 and above to use regular expression. In this tutorial, I will teach you all you need to know to be able to craft powerful time-saving regular expressions.

Basically there are five function to use regular expression such as regexp_count, regexp_instr, regexp_like, regexp_replace, regexp_substr. There are like count, instr, like, replace and substr function but using regular expression (regex).

1. REGEXP_INSTR
   • Description
     REGEXP_INSTR extends the functionality of the INSTR function by letting you search a string for a regular expression pattern.
   • Syntax
     REGEXP_INSTR(<source_string>, <pattern>[[, <start_position>][, <occurrence>][, <return_option>][, <match_parameter>][, <sub_expression>]])
match parameter:
     ‘c’ = case sensitive
     ‘i’ = case insensitive search
     ‘m’ = treats the source string as multiple lines
     ‘n’ = allows the period (.) wild character to match newline
     ‘x’ = ignore whitespace characters
   • Example
     SELECT REGEXP_INSTR('500 Oracle Pkwy, Redwood Shores, CA', '[o][[:alpha:]]{3}', 1, 1, 0, 'i') RESULT
FROM dual;
2. REGEXP_SUBSTR
   • Syntax
     REGEXP_SUBSTR(source_string, pattern
[, position [, occurrence
[, match_parameter]]])
   • Example
     Searches for a comma followed by one or more occurrences of non-comma characters followed by a comma.
     SELECT REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA', ',[^,]+,') RESULT
FROM dual;
3. REGEXP_COUNT
   • Syntax
     REGEXP_COUNT(<source_string>, <pattern>[[, <start_position>], [<match_parameter>]])
match parameter:
     ‘c’ = case sensitive
     ‘i’ = case insensitive search
     ‘m’ = treats the source string as multiple lines
     ‘n’ = allows the period (.) wild character to match newline
     ‘x’ = ignore whitespace characters
   • Example
     Count’s occurrences based on a regular expression.
     SELECT REGEXP_COUNT('2a2abbb', '2a', 1, 'i') RESULT
FROM dual;
4. REGEXP_REPLACE
   • Syntax
     REGEXP_REPLACE(<source_string>, <pattern>,
<replace_string>, <position>, <occurrence>, <match_parameter>)
   • Example
     Replace multiple spaces with a single space.
     SELECT REGEXP_REPLACE('500    Oracle    Parkway, Redwood    Shores, CA', '( ){2,}', ' ') RESULT
FROM dual;
5. REGEXP_LIKE
   • Syntax
     REGEXP_LIKE(<source_string>, <pattern>, <match_parameter>)
   • Example
     AlphaNumeric Characters.
     SELECT *
FROM test
WHERE REGEXP_LIKE(testcol, '[[:alnum:]]');

| | | | | |