Oracle regular expressions: search by pattern exact match

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:

WHERE regexp_like(mail, '@gmail\.com');

the above query will return all users which have an email in @gmail.com domain.

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:

WHERE regexp_like(mail, 'john\.b[a-zA-Z]*@gmail\.com');

some of the results will be what we want but a lot will be just a trash pile. This statement will return users with emails like "mark.john.bradley@gmail.com" or "john.thebadboy@gmail.com.uk". Why? Because regexp_like function checks if the given string contains a substring that matches our regular expression and NOT if the entire string matches the regular expression.

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:

WHERE regexp_like(mail, '^john\.b[a-zA-Z]*@gmail\.com$');

Using regular expressions in Oracle SQL queries, pt. II

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.

Using regular expressions in Oracle on actual tables

Let's say we have this simple table (users):

id login name city email
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

Using regular expressions in Oracle SQL queries, pt. I

This is an article on Oracle and using of regular expressions. Oracle has several regexp functions. Each will be described with an example below.

First things first

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:

  • register at oracle.com
  • download and install latest possible Oracle Database*
* at the moment you can get 10g Express Edition [Universal] here:
Syndicate content