Oracle’s REGEXP Functions

REGEXP_LIKE(source, regexp, modes) is probably the one you'll use most. You can use it in the WHERE and HAVING clauses of a SELECT statement. In a PL/SQL script, it returns a Boolean value. You can also use it in a CHECK constraint. The source parameter is the string or column the regex should be matched against.

SELECT * FROM table1 WHERE REGEXP_LIKE(column1, 'regexp', 'i');

IF REGEXP_LIKE('subject', 'regexp') THEN /* Match */ ELSE /* No match */ END IF;

ALTER TABLE table1 ADD (CONSTRAINT column1_regexp CHECK (REGEXP_LIKE(column1, '^regexp$')));

Example: Returns only the X that hold pure numerical values


Note: You may reverse the result by removing ^ from above statement, like if you want non digit values or [^a-zA-Z]

REGEXP_SUBSTR(source, regexp, position, occurrence, modes) returns a string with the part of source matched by the regular expression. If the match attempt fails, NULL is returned. You can use REGEXP_SUBSTR with a single string or with a column. You can use it in SELECT clauses to retrieve only a certain part of a column. The position parameter specifies the character position in the source string at which the match attempt should start. The first character has position 1.

The occurrence parameter specifies which match to get. Set it to 1 to get the first match. If you specify a higher number, Oracle will continue to attempt to match the regex starting at the end of the previous match, until it found as many matches as you specified. The last match is then returned. If there are fewer matches, NULL is returned. The last three parameters are optional.

SELECT REGEXP_SUBSTR(column1, 'regexp') FROM table1;
match := REGEXP_SUBSTR('subject', 'regexp', 1, 1, 'i')

REGEXP_REPLACE(source, regexp, replacement, position, occurrence, modes) returns the source string with one or all regex matches replaced. If no matches can be found, the original string is replaced. If you specify a positive number for occurrence only that match is replaced. If you specify zero or omit the parameter, all matches are replaced. The last three parameters are optional. The replacement parameter is a string that each regex match will be replaced with.

SELECT REGEXP_REPLACE(column1, 'regexp', 'replacement') FROM table1;

result := REGEXP_REPLACE('subject', 'regexp', 'replacement', 1, 0, 'i');

Example: Replaces everything in the last_name column but just keeps (a-z or A-Z) and a single quote

Option 1
REGEXP_REPLACE(last_name,'[^a-zA-Z'']','') new_last_name

Option 2

Example: Replaces everything in the ZipCode column but just keeps digits

REGEXP_INSTR(source, regexp, position, occurrence, return_option, modes) returns the beginning or ending position of a regex match in the source string. This function takes the same parameters as REGEXP_SUBSTR, plus one more. Set return_option to zero or omit the parameter to get the position of the first character in match. Set it to one to get the position of the first character after the match. The first character in the string has position 1. REGEXP_INSTR returns zero if the match cannot be found. The last 4 parameters are optional.
SELECT REGEXP_INSTR(column1, 'regexp', 1, 1, 0, 'i') FROM table1;

Example: Use regexp_count to determine the number of times that a pattern exists in the string and feed that into the regexp_instr

regexp_instr( str,
              regexp_count( str, '[[:digit:]]')

Option 1: Reverse the string and subtract the position that is found from the length of the string

length(str) - regexp_instr(reverse(str),'[[:digit:]]') + 1

Option 2:

  with x as (
      select '001 My Basic Knowledge' str
        from dual
   select length(str) - regexp_instr(reverse(str),'[[:digit:]]') + 1,
           regexp_instr( str,
                         regexp_count( str, '[[:digit:]]')
   from x