【课堂笔记】【SQL】19.Regular Expression Support

5dm

正则符号集：

 

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

正则函数集：

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

示例：

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