重庆思庄Oracle、Redhat认证学习论坛

标题: 【课堂笔记】【SQL】19.Regular Expression Support [打印本页]

作者: 5dm 时间: 2012-11-6 10:42
标题: 【课堂笔记】【SQL】19.Regular Expression Support

正则符号集：

Symbol

Description

*

Matches zero or more occurrences

|

Alteration operator for specifying alternative matches

^/$

Matches the start-of-line/end-of-line

[ ]

Bracket expression for a matching list matching any one of the expressions represented in the list

{m}

Matches exactly m times

{m,n}

Matches at least m times but no more than n times

[: :]

Specifies a character class and matches any character in that class

\

Can have 4 different meanings: 1. Stand for itself. 2. Quote the next character. 3. Introduce an operator. 4. Do nothing.

+

Matches one or more occurrences

?

Matches zero or one occurrence

.

Matches any character in the supported character set, except NULL

()

Grouping expression, treated as a single subexpression

[==]

Specifies equivalence classes

\n

Back-reference expression

[..]

Specifies one collation element, such as a multicharacter element

作者: 5dm 时间: 2012-11-6 10:44

正则函数集：

REGEXP_LIKE (srcstr, pattern [,match_option])

REGEXP_INSTR (srcstr, pattern [, position [, occurrence

[, return_option [, match_option]]]])

REGEXP_SUBSTR (srcstr, pattern [, position

[, occurrence [, match_option]]])

REGEXP_REPLACE(srcstr, pattern [,replacestr [, position

[, occurrence [, match_option]]]])

作者: 5dm 时间: 2012-11-6 10:53

示例：

SQL> select first_name,last_name from employees
2 where regexp_like (first_name,'^Ste(v|ph)en$');

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Steven               King
Steven               Markle
Stephen              Stiles

SQL> select street_address,regexp_instr(street_address,'[^[:alpha:]]') from locations where regexp_instr(street_address,'[^[:alpha:]]')>1;

STREET_ADDRESS
----------------------------------------
REGEXP_INSTR(STREET_ADDRESS,'[^[:ALPHA:]]')
-------------------------------------------
Magdalen Centre, The Oxford Science Park
9

Schwanthalerstr. 7031
16

Rua Frei Caneca 1360
4

STREET_ADDRESS
----------------------------------------
REGEXP_INSTR(STREET_ADDRESS,'[^[:ALPHA:]]')
-------------------------------------------
Murtenstrasse 921
14

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)

Symbol	Description
*	Matches zero or more occurrences
\|	Alteration operator for specifying alternative matches
^/$	Matches the start-of-line/end-of-line
[ ]	Bracket expression for a matching list matching any one of the expressions represented in the list
{m}	Matches exactly m times
{m,n}	Matches at least m times but no more than n times
[: :]	Specifies a character class and matches any character in that class
\	Can have 4 different meanings: 1. Stand for itself. 2. Quote the next character. 3. Introduce an operator. 4. Do nothing.
+	Matches one or more occurrences
?	Matches zero or one occurrence
.	Matches any character in the supported character set, except NULL
()	Grouping expression, treated as a single subexpression
[==]	Specifies equivalence classes
\n	Back-reference expression
[..]	Specifies one collation element, such as a multicharacter element