Introduction to Regular Expressions in Oracle

38 %
63 %
Information about Introduction to Regular Expressions in Oracle
Technology

Published on March 5, 2014

Author: galobalda

Source: slideshare.net

Description

Presented at ODTUG Kscope13.

Introduction to Regular Expressions in Oracle Galo Balda June 27 2013 – New Orleans, LA

About Me… Database Engineer at the Medicaid Applications Group galo.balda@gmail.com @GaloBalda galobalda.wordpress.com

Agenda  Traditional database pattern matching.  What’s a Regular Expression?  Why should we use Regular Expressions?  Metacharacters. POSIX (ERE)  Oracle SQL Support for Regular Expressions.  Questions.

Traditional Pattern Matching  Simple matching using LIKE with _ and %. select * from all_users where username like ‘%SYS%’ select * from emp where last_name like ‘B_RD’  But LIKE is limited. We end writing complex SQL and PL/SQL.

What is a Regular Expression?  A search pattern using metacharacters and character literals.  A character string (a pattern) that is used to match another string (a search string or target string).  “Wildcards on steroids”.

Brief History…       Initially very popular on UNIX and Perl only. The grep command stands for ‘Global Regular Expression Print’. Web technologies increased its adoption. Java, PHP, XML, Python, etc… have incorporated them. Before 10g using external procedures. MOS Note 119426.1 Available in Oracle 10g.

Why Should We Use Them?  Text search.  Cleaning, manipulation and formatting of data.  Constraints definition.  Enhancing the data flow.

Metacharacters (POSIX ERE)

. (Dot)  Matches any character in the database character set.  Consider the pattern a.b String abb Matches? Yes acb adb acc Yes Yes No

+ (Plus Quantifier)  Matches one or more occurrences of the preceding sub expression.  Consider the pattern hom+e String Matches? hommme some whole Yes No No whome Yes

* (Star Quantifier)  Matches zero or more occurrences of the preceding sub expression.  Consider the pattern hom*e String Matches? hommme shoe hole Yes Yes No whome Yes

? (Question Mark Quantifier)  Matches zero or one occurrences of the preceding sub expression.  Consider the pattern ab?c String Matches? abc ac abbc Yes Yes No adc No

{m} (Interval Exact Count)  Matches exactly m occurrences of the preceding sub expression.  Consider the pattern o{2} String Matches? looking done goool Yes No Yes goaoaol No

{m,} (Interval At-Least Count)  Matches at least m occurrences of the preceding sub expression.  Consider the pattern o{2,} String Matches? tool college goaool Yes No Yes goaoaol No

{m,n} (Interval Between Count)  Matches at least m but not more than n occurrences of the preceding sub expression.  Consider the pattern o{2,4} String Matches? tool college booook Yes No Yes goooool Yes

[char…] Match Character List  Matches any single character in the list within the brackets.  Consider the pattern ab[cde]1 String Matches? abc1 abd1 abe1 Yes Yes Yes adm1 No

[char…] Match Character List  A dash (-) can be used within a bracket expression to define a range.  Consider the pattern ab[c-f][1-5] String Matches? abe3 abd1 abm8 Yes Yes No abf No

[^char…] Non Match Char. List  Matches any single character not in the list within the brackets.  Consider the pattern [^abc]def String Matches? adef kdef edef No Yes Yes 1def Yes

[alt1|alt2] (OR)  Matches either alternative.  Consider the pattern [a|b]cdef String Matches? acdef bcdef abcdef Yes Yes Yes cdef No

(exp) Sub Expression Grouping  Treats the expression within the parentheses as a unit.  Consider the pattern (abc)?def String Matches? abcdef def abcdefg adef Yes Yes Yes Yes

^ (Beginning Of Line Anchor)  Matches the beginning of a string.  Consider the pattern ^(hello) String Matches? hello kscope kscope hello hello Yes No Yes

$ (End Of Line Anchor)  Matches the end of a string.  Consider the pattern (kscope)$ String Matches? hello kscope kscope hello kscope Yes No Yes

(Escape Character)  A backslash () lets you search for a character that would otherwise be treated as a metacharacter.  Consider the pattern hello* String Matches? hello hello* hell* No Yes No

n (Backreference)   A backreference is a numbered reference (in the form 1, 2…9) to the text matching a previous subexpression. Consider the pattern (abc|def)xy1 String Matches? abcxyabc defxydef defxyabc Yes Yes No

Oracle SQL Support For Regexp

REGEXP_LIKE Condition SQL> select ename 2 from emp 3 where ename like 'J%'; ENAME ---------JACQUES JONES JAMES 3 rows selected.

REGEXP_LIKE Condition SQL> select ename 2 from emp 3 where regexp_like (ename,'J(ON|AM)ES$'); ENAME ---------JONES JAMES 2 rows selected.

REGEXP_LIKE Condition SQL> select name 2 from 3 (select 'ALAN' name from dual union all 4 select 'ALLAN' from dual union all 5 select 'LOUISE' from dual union all 6 select 'LILIAN' from dual union all 7 select 'MICHAEL' from dual) 8 where regexp_like (name,'^[^L]*L[^L]*$'); NAME ------ALAN LOUISE MICHAEL 3 rows selected.

REGEXP_LIKE Condition SQL> select name_client from clients; NAME_CLIENT -------------------FOUNTAIN,VICKIE L ORTIZ,GENOVEVA M MALDONADO,TERESA BARAJAS-RAMIREZ,AMY SERRANO,GRACE 5 rows selected.

REGEXP_LIKE Condition SQL> select name_client from clients 2 where regexp_like(name_client,'[^ ]{15,}'); NAME_CLIENT -------------------FOUNTAIN,VICKIE L MALDONADO,TERESA BARAJAS-RAMIREZ,AMY 3 rows selected.

REGEXP_LIKE Condition SQL> alter table clients add phone varchar2(20); Table altered. SQL> alter table clients add constraint ck_phone 2 check 3 ( 4 regexp_like(phone,'^([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9] 5 [0-9][0-9]$') 6 ); Table altered.

REGEXP_LIKE Condition SQL> insert into clients values ('BALDA GALO','(512) 254-1694'); 1 row created. SQL> insert into clients values ('DOE JOHN','512-123-4567'); insert into clients values ('DOE JOHN','512-123-4567') * ERROR at line 1: ORA-02290: check constraint (SCOTT.CK_PHONE) violated

REGEXP_COUNT Function SQL> select regexp_count('I hope you enjoyed Kscope','o') count 2 from dual; COUNT ---------4 1 row selected.

REGEXP_COUNT Function SQL> select 2 regexp_count('I hope you enjoyed Kscope','[^ ]+') words 3 from dual; WORDS ---------5 1 row selected.

REGEXP_INSTR Function SQL> select regexp_instr(sentence,'[^ ]+',1,6) sixth_word 2 from 3 (select 'No sixth word here.' sentence from dual union all 4 select '500 Oracle Parkway, Redwood Shores, CA.' from dual); SIXTH_WORD ---------0 37 2 rows selected.

REGEXP_SUBSTR Function SQL> select nvl(regexp_substr(sentence,'fl(y(ing)?|ew|own|ies)'),'NULL') results 2 from 3 (select 'I usually fly by United.' sentence from dual union all 4 select 'United is flown by thousands of customers.' from dual union all 5 select 'We are flying to San Diego next week.' from dual union all 6 select 'Kscope13 is almost over.' from dual); RESULTS -------------------------------------------------------------------------------fly flown flying NULL 4 rows selected.

REGEXP_SUBSTR Function SQL> variable string varchar2(60); SQL> exec :string := 'I hope you enjoyed Kscope'; SQL> select :string my_string, 2 regexp_substr(:string,'[^ ]+',1,level) word 3 from dual connect by level <= regexp_count(:string,' ') + 1; MY_STRING WORD --------------------------------------I hope you enjoyed Kscope I I hope you enjoyed Kscope hope I hope you enjoyed Kscope you I hope you enjoyed Kscope enjoyed I hope you enjoyed Kscope Kscope 5 rows selected.

REGEXP_REPLACE Function SQL> select 2 regexp_replace('500 Oracle Parkway, Redwood 3 '( ){2,}',' ') results 4 from dual; RESULTS --------------------------------------500 Oracle Parkway, Redwood Shores, CA. 1 row selected. Shores, CA.',

REGEXP_REPLACE Function SQL> select name_client, 2 decode(regexp_count(name_client,' '),1, 3 regexp_replace(name_client,'([^ ]+),([^ ]+) ([^ ]+)','2 3. 1'),0, 4 regexp_replace(name_client,'([^ ]+),([^ ]+)','2 1')) new_name 5 from clients; 1 1 NAME_CLIENT 2 -------------------3 FOUNTAIN,VICKIE L ORTIZ,GENOVEVA M MALDONADO,TERESA BARAJAS-RAMIREZ,AMY SERRANO,GRACE 5 rows selected. 2 NEW_NAME -------------------VICKIE L. FOUNTAIN GENOVEVA M. ORTIZ TERESA MALDONADO AMY BARAJAS-RAMIREZ GRACE SERRANO

Summary  String searching, manipulation, validation, exists in every application that deals with text data; regular expressions are the most sophisticated means of performing such operations.  Starting with Oracle 10g, the database enhances the ability to search for and manipulate text within the database by providing expressive power in queries, data definitions and string manipulations.

References & Reading Material     Oracle Database Advanced Application Developer's Guide 11g Release 2: Using Regular Expressions in Database Applications. Oracle Database SQL Language Reference 11g Release 2. Introducing Oracle Regular Expressions An Oracle White Paper by Peter Linsley. Oracle Regular Expressions Pocket Reference by Jonathan Gennick and Peter Linsley.

Q&A

Add a comment

Related presentations

Related pages

Introduction to regular expressions ... | Oracle Community

I'm well aware that there are already some articles on that topic, some people asked me to share some of my knowledge on this topic. Please take a look at ...
Read more

Introduction to Regular Expressions in Oracle – All Things ...

Regular expressions – RegEx or RegExp to their friends – were fashionably late in coming to the Oracle party. The seeds of what we know today as ...
Read more

Introduction to regular expressions ... - community.oracle.com

After some very positive feedback from Introduction to regular expressions ... I'm now continuing on this topic for the interested audience. As always, if ...
Read more

Introduction - Oracle Help Center

What Are Regular Expressions? Regular expressions are a way to describe a set of strings based on common characteristics shared by each string in the set.
Read more

TWP Regular Expressions - Oracle

Introducing Oracle Regular Expressions Introduction ... perform regular expression based updates within a client and write the results back
Read more

10. Introduction to Regular Expressions - Expert Oracle PL ...

Regular expressions were introduced in Oracle 10 g Release 1, much to the delight of UNIX/Perl programmers everywhere. If you know regular expressions like ...
Read more

Using Regular Expressions - Oracle

Using Regular Expressions . Purpose. In this tutorial, ... Fundamentals 10g Release 2 (10.2), chapter 4 "Using Regular Expressions in Oracle Database". ...
Read more

Regular Expression Metacharacters : Introduction « Regular ...

Regular Expression Metacharacters : Introduction « Regular Expressions Functions « Oracle PL/SQL Tutorial
Read more

Common Regular Expression Patterns - Oracle Blogs

Introduction. Regular Expressions are a powerful way to perform automated string matching. This post goes through some common use cases that can be used in ...
Read more

Using Regular Expressions in Database Applications - Oracle

Oracle Database SQL Language Reference for information about Oracle Database SQL functions for regular expressions. Oracle ... Oracle Database regular ...
Read more