Oracle 10g SQL Fundamentals II--Les08

Regular Expression Support : 

Regular Expression Support

Objectives : 

Objectives After completing this lesson, you should be able to use regular expression support in SQL to search, match, and replace strings all in terms of regular expressions.

Regular Expression Overview : 

Regular Expression Overview ABC A method of describing both simple and complex patterns for searching and manipulating Several new functions to support regular expressions A multilingual regular expression support for SQL and PLSQL string types

Meta Characters : 

Meta Characters

Using Meta Characters : 

Using Meta Characters Problem: Find 'abc' within a string: Solution: 'abc' Matches: abc Does not match: 'def' Problem: To find 'a' followed by any character, followed by 'c' Meta Character: any character is defined by '.' Solution: 'a.c' Matches: abc Matches: adc Matches: alc Matches: a&c Does not match: abb Problem: To find one or more occurrences of 'a' Meta Character: Use'+' sign to match one or more of the previous characters Solution: 'a+' Matches: a Matches: aa Does not match: bbb 1 2 3

Notes Only : 

Notes Only

Regular Expression Functions : 

Regular Expression Functions

The REGEXP Function Syntax : 

The REGEXP Function Syntax 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]]]])

Performing Basic Searches : 

SELECT first_name, last_name FROM employees WHERE REGEXP_LIKE (first_name, '^Ste(v|ph)en$'); Performing Basic Searches

Checking the Presence of a Pattern : 

SELECT street_address, REGEXP_INSTR(street_address,'[^[:alpha:]]') FROM locations WHERE REGEXP_INSTR(street_address,'[^[:alpha:]]')> 1; Checking the Presence of a Pattern

Example of Extracting Substrings : 

SELECT REGEXP_SUBSTR(street_address , ' [^ ]+ ') "Road" FROM locations; Example of Extracting Substrings …

Replacing Patterns : 

SELECT REGEXP_REPLACE( country_name, '(.)', '\1 ') "REGEXP_REPLACE" FROM countries; Replacing Patterns …

Regular Expressions and Check Constraints : 

Regular Expressions and Check Constraints ALTER TABLE emp8 ADD CONSTRAINT email_addr CHECK(REGEXP_LIKE(email,'@'))NOVALIDATE ; INSERT INTO emp8 VALUES (500,'Christian','Patel',   'ChrisP2creme.com', 1234567890, '12-Jan-2004', 'HR_REP', 2000, null, 102, 40) ; 1 2

Summary : 

Summary In this lesson, you should have learned how to use regular expression support in SQL and PL/SQL to search, match, and replace strings all in terms of regular expressions.

Practice 8: Overview : 

Practice 8: Overview This practice covers using regular expressions.