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
SELECT X FROM SO WHERE
REGEXP_LIKE(X, '^[[:digit:]]+$');
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
regexp_replace(last_name,'[^[:alpha:]'']')
Example: Replaces everything in the ZipCode column
but just keeps digits
REGEXP_REPLACE(ZipCode,'^[[:digit:]]+$','')
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
Syntax
regexp_instr( str,
'[[:digit:]]',
1,
regexp_count( str, '[[:digit:]]')
)
'[[:digit:]]',
1,
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,
'[[:digit:]]',
1,
regexp_count( str, '[[:digit:]]')
)
from x
select '001 My Basic Knowledge' str
from dual
)
select length(str) - regexp_instr(reverse(str),'[[:digit:]]') + 1,
regexp_instr( str,
'[[:digit:]]',
1,
regexp_count( str, '[[:digit:]]')
)
from x
