Snippet Name: Regular Expressions: REGEXP_LIKE
Description: REGEXP_LIKE is similar to the LIKE condition, except REGEXP_LIKE performs regular expression matching instead of the simple pattern matching performed by LIKE. This condition evaluates strings using characters as defined by the input character set.
Also see: » Regular Expressions: REGEXP_SUBSTR
» Regular Expressions: REGEXP_REPLACE
» Regular Expressions: RegExp examples t...
» Regular Expressions: RegExp examples t...
» Regular Expressions: RegExp examples o...
» Regular Expressions: Regexp Cheat Sheet
» RegExp: Append first name first letter...
Comment: (none)
Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 14th, 2009
|
REGEXP_LIKE(source_string, pattern [, match_parameter ])
-- examples:
-- Select domains that contain 2 and only 2 letters:
SELECT *
FROM domains
WHERE regexp_like (key, '^[a-z]{2}$');
-- The following query returns the first and last names for
-- those employees with a first name of Steven or Stephen
- (WHERE first_name begins WITH 'Ste' AND ends WITH 'en' AND IN
-- between is either 'v' or 'ph'):
SELECT first_name, last_name
FROM employee_table
WHERE REGEXP_LIKE (first_name, '^Ste(v|ph)en$');
-- This query returns the last name for employees with a
-- double vowel in their last name (where last_name contains
-- two adjacent occurrences of either a, e, i, o, or u,
-- regardless of case):
SELECT last_name
FROM employee_table
WHERE REGEXP_LIKE (last_name, '([aeiou])\1', 'i');
LAST_NAME
-------------------------
De Maab
Greenberg
Soo
Green
Greene
Kloop
-- search for valid email addresses:
SELECT page_content
FROM test_list
WHERE REGEXP_LIKE (page_content, '[A-Z0-9._%-]+@[A-Z0-9._%-]+\.[A-Z]{2,4}');
-- search only for '.com' email addresses:
SELECT email
FROM email_list
WHERE REGEXP_LIKE (email, '[A-Z0-9._%-]+@[A-Z0-9._%-]+\.com');
|