The Power of Regular Expressions in Oracle 10g

1
The Power of Regular Expressions in Oracle 10g

• Presented by Byron Bush
• Presented to HIOUG
• 04-Oct-05

2
What is a Regular Expression?

• Template or pattern for a text string
• Indicates, in general terms, what characteristics
  the text must have to fit its template
• A special string for describing a search pattern
• You can think of Regular Expressions as wildcards
  on steroids!

3
You probably already use simple Regular
Expressions

• dir .txt (Windows / DOS)
• ls .txt (Linux / Unix)
• dir ??report.pdf (Windows / DOS)
• ls ??report. (Linux / Unix)

4
Tools that use Regular Expressions

• Grep
• Egrep
• Sed
• Awk
• Most Unix / Linux command line tools
• Most programming languages on most platforms
• Oracle! (Starting in 10g)

5
Regular Expressions Warning!

• Each tool supports its own variation of Regular
  Expression syntax.
• Be very careful when porting Regular Expression
  code from one platform / tool / language to
  another!

6
Why use Regular Expressions in Oracle?

• Replace extremely complex select statement
  logic
• Replace Like statements in the Where clause
  with more complex logic
• Replace extremely complex PL/SQL code used to
  validate data format in triggers and constraints

7
Basic Regular Expression Meta-Characters
8
Oracle Regular Expression Tools
9
RegExp_Demo Table

• Insert code to create demo table
• Full Name
• Phone number
• Sex
• Age
• Salary
• Street, City, State, Zip
• IP Address
• Email Address
• Favorite Saying

10
Populate RegExp_Demo Table

• Insert statements for RegExp_Demo table

11
RegExp_Like

• RegExp_Like is a function and can be used
  anywhere you would use a function
• Returns rows matching the Pattern
• Well be using this primarily in the where
  clause

12
RegExp_Like Arguements

• Has the following IN parameters
• Source String
• char, varchar2, clob, etc
• Pattern
• a regular expression
• Match Pattern (changes matching behavior)
• i -gt case insensitive matching
• c -gt case sensitive matching
• n -gt allows . to match a new line character
• m -gt treats the source string as multiple lines

13
RegExp_Like Examples
14
Posix Character Classes
15
RegExp_Like Examples

• Now well use RegExp_Like to Query data!
• Find_All_Females.sql
• 808_Phone_Numbers.sql
• Find_States.sql
• Email_addresses.sql
• IP_Addresses.sql
• Other_Examples.sql
• Duplicate_words.sql

16
RegExp_Instr

• RegExp_Like is a function and can be used
  anywhere you would use a function
• Returns an integer indicating either
• The beginning position of the pattern
• The position of the character following the
  pattern

17
RegExp_Instr Arguements

• Has the following IN parameters
• Source String
• char, varchar2, clob, etc
• Pattern
• a regular expression
• Position
• where in the Source String to begin the search.
  Default is 1.
• Occurrence
• Positive integer indicating which occurrence of
  Pattern in Source String Oracle should search
  for. Default is 1.
• Return Option (you specify what Oracle should
  return)
• 0 return position of first character in the
  occurrence
• 1 return position of character following the
  occurrence
• Match Pattern (changes matching behavior)
• i -gt case insensitive matching
• c -gt case sensitive matching
• n -gt allows . to match a new line character
• m -gt treats the source string as multiple lines

18
RegExp_Instr Examples

• Now well use RegExp_Instr to Query data!
• Regexp_Instr_Examples.sql

19
RegExp_Replace

• RegExp_Like is a function and can be used
  anywhere you would use a function
• Returns
• By default, the Source String that matches the
  regular expression replace by the Replace String
• Returns a Varchar2 if the first argument is not a
  LOB
• Returns a CLOB if the first argument is a LOB

20
RegExp_Replace Arguements

• Has the following IN parameters
• Source String
• char, varchar2, clob, etc
• Pattern
• a regular expression
• Replace String
• char, varchar2, clob, etc
• May contain up to 500 back references
• Position
• Positive integer indicating which occurrence of
  Pattern in Source String Oracle should replace.
  Default is 1.
• If 0 then Oracle replaces all occurrences of the
  match
• Match Pattern (changes matching behavior)
• i -gt case insensitive matching
• c -gt case sensitive matching
• n -gt allows . to match a new line character
• m -gt treats the source string as multiple lines

21
RegExp_Replace Examples

• Its EXTREMELY important to know about Back
  References when using RegExp_Replace!
• Now well use RegExp_Replace to Query data!
• Regexp_Replace_Examples.sql

22
RegExp_Substr

• RegExp_Substr is a function and can be used
  anywhere you would use a function
• Similar to RegExp_Instr, but instead of returning
  the position of a substring, it returns the
  substring itself as a Varchar2 or CLOB

23
RegExp_Substr Arguements

• Has the following IN parameters
• Source String
• char, varchar2, clob, etc
• Pattern
• a regular expression
• Position
• where in the Source String to begin the search.
  Default is 1.
• Occurrence
• Positive integer indicating which occurrence of
  Pattern in Source String Oracle should search
  for. Default is 1.
• Return Option (you specify what Oracle should
  return)
• 0 return position of first character in the
  occurrence
• 1 return position of character following the
  occurrence
• Match Pattern (changes matching behavior)
• i -gt case insensitive matching
• c -gt case sensitive matching
• n -gt allows . to match a new line character
• m -gt treats the source string as multiple lines

24
RegExp_Substr Examples

• Now that weve gone through a LOT of regular
  expression examples use of RegExp_Substr should
  be quite EASY!
• Regexp_Substr_Examples.sql

25
Regular Expression References

• Mastering Regular Expressions
• By Jeffrey E. F. Friedl
• OReilly
• Regular Expressions in 10 Minutes
• By Ben Forta
• Sams
• Oracle Regular Expressions Pocket Reference
• By Johathan Gennick
• OReilly

26
Thats It!

• Have fun with Regular Expressions
• Try to integrate REs into your normal business
• Questions?