SQL 基礎正規表達式（二十三）

在SQL 和 PL/SQL 中使用正規表達式

函數名稱

描述

REGEXP_LIKE

與LIKE運算符類似，但執行正規表達式比對，而不是簡單的模糊比對（條件）

REGEXP_REPLACE

以正規表達式搜尋和替換字元串

REGEXP_INSTR

以正規表達式搜尋字元串，并傳回比對的位置

REGEXP_SUBSTR

以正規表達式搜尋和提取比對字元串

REGEXP_COUNT

傳回比對的次數

什麼是元字元？

元字元是特殊字元有特殊的含義，如一個通配符，重複字元，一個不比對的字元，一個範圍内的符。

您可以使用多個預定義的元字元符号的模式比對。

例如, ^(f|ht)tps?:$ 正規表達式搜尋字元串從以下開始：

– 字面值 f 或 ht

– 字面值 t

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

– 冒号“:” 結尾的字面值

正規表達式的元字元

文法

.

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

\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 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 執行基本搜尋

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 替換

[, 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 函數提取字元串

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

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

子表達式

<a href="https://s5.51cto.com/wyfs02/M00/8F/40/wKioL1jY2dew11LbAAB8EA0z2kA455.jpg" target="_blank"></a>

使用子表達式與正規表達式支援

SELECT

('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;

為什麼要通路第n個子表達式

一個更實際的用途：DNA測序

您可能需要找到一個特定的子模式，确定了在小鼠DNA免疫

所需的蛋白質。

SELECT REGEXP_INSTR(' ccacctttccctccactcctcacgttctcacctgtaaagcgtccctc

cctcatccccatgcccccttaccctgcagggtagagtaggctagaaaccagagagctccaagc

tccatctgtggagaggtgccatccttgggctgcagagagaggagaatttgccccaaagctgcc

tgcagagcttcaccacccttagtctcacaaagccttgagttcatagcatttcttgagttttca

ccctgcccagcaggacactgcagcacccaaagggcttcccaggagtagggttgccctcaagag

gctcttgggtctgatggccacatcctggaattgttttcaagttgatggtcacagccctgaggc

atgtaggggcgtggggatgcgctctgctctgctctcctctcctgaacccctgaaccctctggc

taccccagagcacttagagccag ',

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

1, 1, 0, 'i',

1) "Position"

REGEXP_SUBSTR 示例

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

'acg(.*)gca', -- regular expression pattern

1) -- sub-expression

"Value"

使用 REGEXP_COUNT函數

SELECT REGEXP_COUNT(

'ccacctttccctccactcctcacgttctcacctgtaaagcgtccctccctcatccccatgcccccttaccctgcag

ggtagagtaggctagaaaccagagagctccaagctccatctgtggagaggtgccatccttgggctgcagagagaggag

aatttgccccaaagctgcctgcagagcttcaccacccttagtctcacaaagccttgagttcatagcatttcttgagtt

ttcaccctgcccagcaggacactgcagcacccaaagggcttcccaggagtagggttgccctcaagaggctcttgggtc

tgatggccacatcctggaattgttttcaagttgatggtcacagccctgaggcatgtaggggcgtggggatgcgctctg

ctctgctctcctctcctgaacccctgaaccctctggctaccccagagcacttagagccag' ,

'gtc') AS Count

Check限制和正規表達式：示例

ALTER TABLE emp8

ADD CONSTRAINT email_addr

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

<a href="https://s4.51cto.com/wyfs02/M01/8F/40/wKioL1jY21OBm-2HAACOiagkBbY493.jpg" target="_blank"></a>

本文轉自 yuri_cto 51CTO部落格，原文連結：http://blog.51cto.com/laobaiv1/1910840，如需轉載請自行聯系原作者