Regular expressions are patterns used to match character combinations in strings. Oracle 10g introduced support for regular expressions using different functions. This post focuses on the Oracle REGEXP_LIKE function, and explains how to use it.
Description
the Oracle REGEXP_LIKE is used to perform a regular expression matching (rather than a simple pattern matching performed by LIKE).
syntax
1
| REGEXP_LIKE ( string expression, pattern [, matching parameter ] ) |
- string expression – the string expression.
- pattern – the regular expression matching pattern
- match parameter – lets you to change the default matching behaviour of the Oracle REGEXP_LIKE function (for example, change the search from case sensitive to case insensitive).
Basic Oracle REGEXP_LIKE Examples
We’ll start by creating a table called Names, based on its values, the following Oracle REGEXP_LIKE examples will perform different regular expression searches.
1
2
3
4
5
| CREATE TABLE names AS SELECT last_name AS NAME FROM hr.employees ORDER BY salary ; |
The following Oracle REGEXP_LIKE example would retrieve all of the names that contain the letter ‘z’. This Oracle SELECT statement actually puts no lower or upper limit on the number of letters before or after the letter ‘z’ (any number of characters is allowed), but requires the word to contain the letter ‘z’.
01
02
03
04
05
06
07
08
09
10
| SELECT * FROM names WHERE regexp_like ( name , 'z' ) ; NAME ------------------------- Lorentz Gietz Ozer |
The next Oracle REGEXP_LIKE example would retrieve all of the names that contain the letter-sequence ‘be’. Again, this Oracle SELECT statement actually puts no lower or upper limit on the number of letters before or after the letter-sequence ‘be’ (any number of characters is allowed), but requires the word to contain the letter-sequence ‘be’.
1
2
3
4
5
6
7
8
| SELECT * FROM names WHERE regexp_like ( name , 'be' ) ; NAME --------------------------- Abel Greenberg |
using the pipe (|) operator
The Pipe operator (|) is used to specify alternative matches. In the next Oracle REGEXP_LIKE example we would use the pipe operator (|) in order to retrieve all of the names that contain the letter-sequence ‘be’ or ‘ae’. This Oracle SELECT statement actually puts no lower or upper limit on the number of letters before or after the letter-sequence ‘be’ or ‘ae'(any number of characters is allowed), but requires the word to contain these sequences.
01
02
03
04
05
06
07
08
09
10
| SELECT * FROM names WHERE regexp_like ( name , 'be|ae' ) ; NAME ------------------------- Baer Abel Raphaely Greenberg |
By specifying the letter ‘c’ (as the third argument of the REGEXP_LIKE function) we can make a case sensitive search (the default in Oracle).
01
02
03
04
05
06
07
08
09
10
| SELECT * FROM names WHERE regexp_like ( name , 'be|ae' , 'c' ) ; NAME ------------------------- Baer Abel Raphaely Greenberg |
And by specifying the letter ‘i’ (as the third argument of the REGEXP_LIKE function) we can make a case insensitive search.
01
02
03
04
05
06
07
08
09
10
11
12
| SELECT * FROM names WHERE regexp_like ( name , 'be|ae' , 'i' ) ; NAME ------------------------- Bell Bernstein Baer Abel Raphaely Greenberg |
Using the Caret(^) operator
We can use the caret (^) operator to indicate a beginning-of-line character, in this REGEXP_LIKE example we would retrieve all names that start with the letter-sequence ‘be’ or ‘ba’ (case insensitive search)
01
02
03
04
05
06
07
08
09
10
11
12
| SELECT * FROM names WHERE regexp_like ( name , '^be|^ba' , 'i' ) ; NAME ------------------------- Baida Bell Banda Bates Bernstein Baer |
Using the Dollar ($) operator
We can use the dollar ($) operator to indicate an end-of-line character, in this REGEXP_LIKE example we would retrieve all names that end with the letter-sequence ‘es’ or ‘er’ (case insensitive search).
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
| SELECT * FROM names WHERE regexp_like ( name , 'es$|er$' , 'i' ) ; NAME ------------------------- Philtanker Colmenares Jones Gates Davies Nayer Stiles Dellinger Bates Baer |
Using Square Brackets
We can use the Square Brackets to specify a matching list that should match any one of the expressions represented in it. The next Oracle REGEXP_LIKE example would retrieve all names that contain the letters ‘j’ or ‘z’.
01
02
03
04
05
06
07
08
09
10
11
| SELECT * FROM names WHERE regexp_like ( name , '[jz]' ) ; NAME ------------------------- Rajs Lorentz Gietz Ozer Errazuriz |
This REGEXP_LIKE example would retrieve all names that contain the letters ‘b’ or ‘z’ or ‘E’ (case sensitive search)
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
| SELECT * FROM names WHERE regexp_like ( name , '[bzE]' ) ; NAME ------------------------- Tobias Cabrio Everett Lorentz Pataballa Ernst Cambrault Gietz McEwen Cambrault |
Next, we’ll modify our last query and make it a case insensitive search :
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
| SELECT * FROM names WHERE regexp_like ( name , '[bzE]' , 'i' ) ; NAME ------------------------- Philtanker Zachary Markle Gee Perkins Colmenares Patel OConnell Mikkilineni Tobias Seo |
This Oracle REGEXP_LIKE example would retrieve all the names that contain the letters ‘a’, ‘b’, or ‘c’ :
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
| SELECT * FROM names WHERE regexp_like ( name , '[abc]' ) ; NAME ------------------------- Philtanker Markle Landry Colmenares Patel Vargas Sullivan Marlow Grant Matos |
And instead of specifying the letters ‘a’, ‘b’ and ‘c’ separately, we can specify a range :
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
| SELECT * FROM names WHERE regexp_like ( name , '[a-c]' ) ; NAME ------------------------- Philtanker Markle Landry Colmenares Patel Vargas Sullivan Marlow Grant Matos |
The next Oracle REGEP_LIKE example would retrieve all names that contain a letter in the range of ‘d’ and ‘g’, followed by the letter ‘a’.
01
02
03
04
05
06
07
08
09
10
| SELECT * FROM names WHERE regexp_like ( name , '[d-g]a' ) ; NAME ------------------------- Vargas Baida Fleaur Banda |
Using the Period (.) Operator
The period (.) operator matches any character except NULL, the next Oracle REGEXP_LIKE example would retrieve all names that contain a letter in the range of ‘b’ and ‘g’, followed by any character, followed by the letter ‘a’.
01
02
03
04
05
06
07
08
09
10
11
12
13
14
| SELECT * FROM names WHERE regexp_like ( name , '[b-g].[a]' ) ; NAME ------------------------- Colmenares Tobias McCain Sarchand Sewall Cambrault Sciarra Cambrault |
We can use the Period Operator to represent more than one character, the next Oracle REGEXP_LIKE example would retrieve all names that contain a letter in the range of ‘b’ and ‘g’, followed by any two characters, followed by the letter ‘a’.
1
2
3
4
5
6
7
8
| SELECT * FROM names WHERE regexp_like ( name , '[b-g]..[a]' ) ; NAME ------------------------- De Haan Kochhar |
Using the curly brackets
The curly brackets are used to specify an exact number of occurrences, for example display all names that contain double ‘o’ letters.
1
2
3
4
5
6
7
8
| SELECT * FROM names WHERE regexp_like ( name , '[o]{2}' ) ; NAME ------------------------- Khoo Bloom |