Here you can learn about finding the exact regular expression match using SQL query in Oracle. Article is made upon examples so you will be on the right track in no time.
Usually when we want to search Oracle database users table via regular expressions by email domain filter, it's easy:
SELECT * FROM users WHERE regexp_like(mail, '@gmail\.com');
But lets say that we want to be more specific and we want to search the database for users whose name is John and last name is starting with "B". For this example, lets assume that some users email address format is "firstname.lastname@gmail.com".
If we use this statement:
SELECT * FROM users WHERE regexp_like(mail, 'john\.b[a-zA-Z]*@gmail\.com');
The solution to this problem i very simple. We must add two special characters. One, ^ (caret), at the beginning and the other, $ (dollar), at the end of our regular expression.
Quick explanation:
^ (caret) - means that the string starts with regular expression pattern.
$ (dollar) - means that the string ends with regular expression pattern.
Knowing this correct statement should look like this:
SELECT * FROM users WHERE regexp_like(mail, '^john\.b[a-zA-Z]*@gmail\.com$');
This is an article on Oracle and using of regular expressions. Oracle has several regexp functions. Each will be described with an example below.
If you have access to existing Oracle database you can skip to the next paragraph.
If you don’t have Oracle database installed already, please follow these steps:
Basically they work in exactly same way as other Oracle methods.
source - source string.
regexp - regular expression.
position - position of the character in the source string at which the match should start.
occurrence - specifies witch match to get from source string.
replacement - in REGEXP_REPLACE specifies what is the replacement for matched regexp in source string. If omitted, matching regexp value will be deleted.
return_option - in REGEXP_INSTR: set to 0 to get position of first character in match, set to 1 to get position of first character after the match.
mode - is a string of up to three (from four: i, c, n, m) characters.
REGEXP_LIKE(source, regexp, modes*)
SELECT 'true' FROM dual WHERE REGEXP_LIKE('Hello world', 'l{1,2}');
REGEXP_SUBSTR(source, regexp, position*, occurrence*, modes*)
SELECT REGEXP_SUBSTR('Hello World!', 'W([a-z]+)') FROM dual;
REGEXP_REPLACE(source, regexp, replacement*, position*, occurrence*, modes*)
SELECT REGEXP_REPLACE('Hello World!', 'W([a-z]+)') FROM dual;
REGEXP_INSTR(source, regexp, position*, occurrence*, return_option*, modes*)
SELECT REGEXP_INSTR('Hello World!', '([A-Z])', 1, 2, 0, 'c') FROM dual;
REGEXP_COUNT(source, regexp, position*, mode*) **
* optional parameter
** not available in Oracle 10g Express Edition
Thanks for all your comments.
This is a second part of an article on regular expressions usage in Oracle. This part contains SQL examples only, for more details about functions and their parameters see first part of this article.
Regular expressions used in examples are very simple as this is no publication about regular expressions itself.
Let's say we have this simple table (users):
id | login | name | city | |
---|---|---|---|---|
1 | jd | John Doe | Washington | j.doe@abcxyz.com |
2 | ivan | Ivan Ivanovich | Moscow | ivan@qwerty.ru |
3 | jane84 | Jane Doe | Washington | jane@abcxyz.com |
4 | jn2501 | Juan Nadie | Madrit | juan@nadie.biz |
SELECT name FROM users WHERE REGEXP_LIKE( city , 'M.+' );
Result:
name ---- Ivan Ivanovich Juan Nadie
SELECT DISTINCT REGEXP_SUBSTR ( email , '@([a-z]+)' ) FROM users;
Result:
@abcxyz @qwerty @nadie
SELECT REGEXP_REPLACE ( email , '@' , ' at ' ) FROM users;
j.doe at abcxyz.com ivan at qwerty.ru jane at abcxyz.com juan at nadie.biz
SELECT id, login, name, city, email FROM users WHERE REGEXP_INSTR ( email , '.' ) < REGEXP_INSTR ( email , '@' );
Result:
id login name city email -- ----- -------- ---------- ---------------- 1 jd John Doe Washington j.doe@abcxyz.com
SELECT login, REGEXP_COUNT( login , '[0-9]' ) AS num_count FROM users;
Result:
login num_count ----- ---- jd 0 ivan 0 jane84 2 jn2501 4