溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》

SQL 基礎(chǔ)正則表達(dá)式（二十三）

發(fā)布時(shí)間：2020-07-17 09:11:14 來(lái)源：網(wǎng)絡(luò) 閱讀：419 作者：yuri_cto 欄目：數(shù)據(jù)庫(kù)

在SQL 和 PL/SQL 中使用正則表達(dá)式

函數(shù)名稱(chēng)描述
REGEXP_LIKE與LIKE運(yùn)算符類(lèi)似，但執(zhí)行正則表達(dá)式匹配，而不是簡(jiǎn)單的模糊匹配（條件）
REGEXP_REPLACE以正則表達(dá)式搜索和替換字符串
REGEXP_INSTR以正則表達(dá)式搜索字符串，并返回匹配的位置
REGEXP_SUBSTR以正則表達(dá)式搜索和提取匹配字符串
REGEXP_COUNT返回匹配的次數(shù)


什么是元字符？

元字符是特殊字符有特殊的含義，如一個(gè)通配符，重復(fù)字符，一個(gè)不匹配的字符，一個(gè)范圍內(nèi)的符。

您可以使用多個(gè)預(yù)定義的元字符符號(hào)的模式匹配。

例如, ^(f|ht)tps?:$ 正則表達(dá)式搜索字符串從以下開(kāi)始：

– 字面值 f 或 ht

– 字面值 t

– 字面值 p,字面值s 可選

– 冒號(hào)“:” 結(jié)尾的字面值


正則表達(dá)式的元字符

語(yǔ)法描述
.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]]])


使用REGEXP_LIKE 執(zhí)行基本搜索

REGEXP_LIKE(source_char, pattern [, match_parameter ])


SELECT first_name, last_name FROM employees

WHERE REGEXP_LIKE (first_name, '^Ste(v|ph)en$');


使用REGEXP_REPLACE 替換

REGEXP_REPLACE(source_char, pattern [,replacestr

[, position [, occurrence [, match_option]]]])


SELECT REGEXP_REPLACE(phone_number, '\.','-') AS phone

FROM employees;


使用 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;


使用 REGEXP_SUBSTR 函數(shù)提取字符串

REGEXP_SUBSTR (source_char, pattern [, position [, occurrence [, match_option]]])


SELECT REGEXP_SUBSTR(street_address , ' [^ ]+ ') AS Road FROM locations;


子表達(dá)式

SQL 基礎(chǔ)正則表達(dá)式（二十三）


使用子表達(dá)式與正則表達(dá)式支持

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

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

1) -- sub-expression on which to search

"Position"

FROM dual;


為什么要訪問(wèn)第n個(gè)子表達(dá)式

一個(gè)更實(shí)際的用途：DNA測(cè)序

您可能需要找到一個(gè)特定的子模式，確定了在小鼠DNA免疫

所需的蛋白質(zhì)。


SELECT REGEXP_INSTR(' ccacctttccctccactcctcacgttctcacctgtaaagcgtccctc

cctcatccccatgcccccttaccctgcagggtagagtaggctagaaaccagagagctccaagc

tccatctgtggagaggtgccatccttgggctgcagagagaggagaatttgccccaaagctgcc

tgcagagcttcaccacccttagtctcacaaagccttgagttcatagcatttcttgagttttca

ccctgcccagcaggacactgcagcacccaaagggcttcccaggagtagggttgccctcaagag

gctcttgggtctgatggccacatcctggaattgttttcaagttgatggtcacagccctgaggc

atgtaggggcgtggggatgcgctctgctctgctctcctctcctgaacccctgaaccctctggc

taccccagagcacttagagccag ',

'(gtc(tcac)(aaag))',

1, 1, 0, 'i',

1) "Position"

FROM dual;


REGEXP_SUBSTR 示例

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;


使用 REGEXP_COUNT函數(shù)

REGEXP_COUNT (source_char, pattern [, position

[, occurrence [, match_option]]])


SELECT REGEXP_COUNT(

'ccacctttccctccactcctcacgttctcacctgtaaagcgtccctccctcatccccatgcccccttaccctgcag

ggtagagtaggctagaaaccagagagctccaagctccatctgtggagaggtgccatccttgggctgcagagagaggag

aatttgccccaaagctgcctgcagagcttcaccacccttagtctcacaaagccttgagttcatagcatttcttgagtt

ttcaccctgcccagcaggacactgcagcacccaaagggcttcccaggagtagggttgccctcaagaggctcttgggtc

tgatggccacatcctggaattgttttcaagttgatggtcacagccctgaggcatgtaggggcgtggggatgcgctctg

ctctgctctcctctcctgaacccctgaaccctctggctaccccagagcacttagagccag' ,

'gtc') AS Count

FROM dual;


Check約束和正則表達(dá)式：示例

ALTER TABLE emp8

ADD CONSTRAINT email_addr

CHECK(REGEXP_LIKE(email,'@')) NOVALIDATE;

SQL 基礎(chǔ)正則表達(dá)式（二十三）



向AI問(wèn)一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手