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$');