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
|
Word To Word
The cap brackets '^' in start, '(' single bar '|' in center of each word ')' and dollar sign '$' in end are used to specify an exact string occurrences, for example display names that contain only 'Khoo'.
1
2
3
4
5
6
7
8
|
SELECT *
FROM names
WHERE regexp_like (name , '^(Khoo|Word1|Word2)$');
NAME
Khoo
|
