SQL Base Regular Expression (23)

Using regular expressions in SQL and PL/n

Function name	Describe
Regexp_like	Similar to the LIKE operator but performs regular expression matching instead of a simple fuzzy match (condition)
Regexp_replace	Search and replace strings with regular expressions
Regexp_instr	Searches for strings in regular expressions and returns matching locations
Regexp_substr	Search and extract matching strings in regular expressions
Regexp_count	Returns the number of matches

What is a meta-character?

Metacharacters is a special character that has a special meaning, such as a wildcard, a repeating character, a mismatched character, and a range of characters.

You can use multiple predefined meta-character symbols for pattern matching.

For example, the ^ (F|HT) tps?:$ Regular Expression search string starts with the following:

– literal F or HT

– Literal value T

– literal value p, s optional

– The literal value at the end of the colon ":"

The meta-character of a regular expression

Grammar	Describe
.	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 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
\ n	Matches 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.
\d	A Digit Character
[: Class:]	Matches any character belonging to the specified POSIX character class
[^:class:]	Matches any single character 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;

Inserting with 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;

Extracting a string using the REGEXP_SUBSTR function

Regexp_substr (Source_char, pattern [, position [, occurrence [, match_option]])

SELECT regexp_substr (street_address, ' [^]+ ') as Road from locations;

Sub-expression

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M00/8F/40/wKioL1jY2dew11LbAAB8EA0z2kA455.jpg "title=" Qq20170327140526.jpg "width=" 720 "height=" 332 "border=" 0 "hspace=" 0 "vspace=" 0 "style=" WIDTH:720PX;HEIGHT:332PX; "alt = "Wkiol1jy2dew11lbaab8ea0z2ka455.jpg"/>

Using sub-expressions and regular expression support

SELECT

Regexp_instr

(' 0123456789 ',--source char or search value

' (123) (4 (+)) ',--Regular expression patterns

1,--position to start searching

1,--Occurrence

0,--return option

' I ',--match option (case insensitive)

1)--sub-expression on which to search

"Position"

from dual;

Why to access the nth sub-expression

A more practical use: DNA sequencing

You may need to find a specific sub-pattern that determines the DNA immunity in mice

The required protein.

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

' I ',--match option (case insensitive)

1)--sub-expression

"Value"

from dual;

Using 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: an example

ALTER TABLE EMP8

ADD CONSTRAINT email_addr

CHECK (regexp_like (email, ' @ ')) novalidate;

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M01/8F/40/wKioL1jY21OBm-2HAACOiagkBbY493.jpg "title=" Qq20170327140526.jpg "width=" 720 "height=" 305 "border=" 0 "hspace=" 0 "vspace=" 0 "style=" width:720px;height:305px; "alt = "Wkiol1jy21obm-2haacoiagkbby493.jpg"/>

This article is from the "record a bit of learning life" blog, please make sure to keep this source http://ureysky.blog.51cto.com/2893832/1910840

SQL Base Regular Expression (23)