SQL basic regular expressions (23)
Using regular expressions in SQL and PL/SQL
The function name describes REGEXP_LIKE similar to the LIKE operator, but performs regular expression matching instead of simple fuzzy matching (conditional) REGEXP_REPLACE searches for and replaces strings with regular expressions REGEXP_INSTR searches for strings with regular expressions, and returns matching positions REGEXP_SUBSTR searches and extracts matching strings REGEXP_COUNT returns the number of matches
What are metacharacters?
Metacharacters are special characters that have special meanings, such as a wildcard, a repeating character, a mismatched character, and a character within a range.
You can use pattern matching of multiple predefined metacharacter symbols.
For example, ^ (f | ht) tps?:$ regular expression search strings start with the following:
-literal value f or ht
-literal value t
-literal value p, literal value s optional
-the literal value at the end of the colon ":"
Metacharacters of regular expressions
Syntax description. Matches any character in the supported character set, except NULL+Matches one or more occurrences?Matches zero or one occurrence*Matches zero or more occurrences of the preceding subexpression {m} Matches exactly m occurrences of the preceding expression {m,} Matches at least m occurrences of the preceding subexpression {m ·n} Matches at least m, but not more than n, occurrences of the preceding
Subexpression
[.] Matches any single character in the list within the brackets | Matches one of the alternatives (...) Treats the enclosed expression within the parentheses as a unit. The
Subexpression can be a string of literals or a complex expression containing
Operators.
^ Matches the beginning of a string$Matches the end of a string\ Treats the subsequent metacharacter in the expression as a literal\ nMatches the nth (1-9) preceding subexpression of whatever is grouped
Within parentheses. The parentheses cause an expression to be
Remembered; a backreference refers to it.
\ dA digit character [: class:] Matches any character belonging to the specified POSIX character class [^: class:] Matches any single character not in the list within the brackets
REGEXP_LIKE (source_char, pattern [, match_option]
REGEXP_INSTR (source_char, pattern [, position)
[, occurrence [, return_option
[, match_option [, subexpr])
REGEXP_SUBSTR (source_char, pattern [, position)
[, occurrence [, match_option
[, subexpr])
REGEXP_REPLACE (source_char, pattern [, replacestr)
[, position [, occurrence
[, match_option])
REGEXP_COUNT (source_char, pattern [, position)
[, occurrence [, match_option]])
Perform a basic search using REGEXP_LIKE
REGEXP_LIKE (source_char, pattern [, match_parameter])
SELECT first_name, last_name FROM employees
WHERE REGEXP_LIKE (first_name,'^ Ste (v | ph) en$')
Replace with REGEXP_REPLACE
REGEXP_REPLACE (source_char, pattern [, replacestr)
[, position [, occurrence [, match_option])
SELECT REGEXP_REPLACE (phone_number,'\.','-') AS phone
FROM employees
Insert using REGEXP_INSTR
REGEXP_INSTR (source_char, pattern [, position [
Occurrence [, return_option [, match_option])
SELECT street_address,REGEXP_INSTR (street_address,' [[: alpha:]]') AS
First_Alpha_Position
FROM locations
Use the REGEXP_SUBSTR function to extract strings
REGEXP_SUBSTR (source_char, pattern [, position [, occurrence [, match_option])
SELECT REGEXP_SUBSTR (street_address,'[^] +') AS Road FROM locations
Subexpression
Support using subexpressions and regular expressions
SELECT
REGEXP_INSTR
('0123456789,-- source char or search value
'(123) (4 (56) (78))'-- regular expression patterns
1.-- position to start searching
1.-- occurrence
0,-- return option
'ified,-match option (case insensitive)
1)-- sub-expression on which to search
"Position"
FROM dual
Why visit the nth sub-expression?
A more practical use: DNA sequencing
You may need to find a specific subpattern that determines DNA immunity in mice.
The protein you need.
SELECT REGEXP_INSTR ('ccacctttccctccactcctcacgttctcacctgtaaagcgtccctc
Cctcatccccatgcccccttaccctgcagggtagagtaggctagaaaccagagagctccaagc
Tccatctgtggagaggtgccatccttgggctgcagagagaggagaatttgccccaaagctgcc
Tgcagagcttcaccacccttagtctcacaaagccttgagttcatagcatttcttgagttttca
Ccctgcccagcaggacactgcagcacccaaagggcttcccaggagtagggttgccctcaagag
Gctcttgggtctgatggccacatcctggaattgttttcaagttgatggtcacagccctgaggc
Atgtaggggcgtggggatgcgctctgctctgctctcctctcctgaacccctgaaccctctggc
Taccccagagcacttagagccag'
'(gtc (tcac) (aaag))'
1, 1, 0,'i'
1) "Position"
FROM dual
REGEXP_SUBSTR example
SELECT
REGEXP_SUBSTR
('acgctgcactgca',-- source char or search value
'acg (. *) gca',-- regular expression pattern
1.-- position to start searching
1.-- occurrence
'ified,-match option (case insensitive)
1)-- sub-expression
"Value"
FROM dual
Use the REGEXP_COUNT function
REGEXP_COUNT (source_char, pattern [, position)
[, occurrence [, match_option]])
SELECT REGEXP_COUNT (
'ccacctttccctccactcctcacgttctcacctgtaaagcgtccctccctcatccccatgcccccttaccctgcag
Ggtagagtaggctagaaaccagagagctccaagctccatctgtggagaggtgccatccttgggctgcagagagaggag
Aatttgccccaaagctgcctgcagagcttcaccacccttagtctcacaaagccttgagttcatagcatttcttgagtt
Ttcaccctgcccagcaggacactgcagcacccaaagggcttcccaggagtagggttgccctcaagaggctcttgggtc
Tgatggccacatcctggaattgttttcaagttgatggtcacagccctgaggcatgtaggggcgtggggatgcgctctg
Ctctgctctcctctcctgaacccctgaaccctctggctaccccagagcacttagagccag'
'gtc') AS Count
FROM dual
Check constraints and regular expressions: exampl
ALTER TABLE emp8
ADD CONSTRAINT email_addr
CHECK (REGEXP_LIKE (email,'@')) NOVALIDATE
