Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.