Regexp_like Examples | How to use Regexp_like in SQL?

REGEXP_LIKE Examples :

In my previous article, I have explained different regular expressions with its description. In this article, I will try to give the regular expressions, which are used for pattern matching purpose. Regular expressions are patterns used to match character combinations in strings. In this article I would like to give you multiple Regexp_like Examples in real industry.REGEXP_LIKE function is one of the important regular expression, which is used for regular expression matching. This article will give you idea about different REGEXP_LIKE examples.

REGEXP_LIKE function is same as Like operator, which is used for matching of regular expressions.

REGEXP_LIKE Syntax:

REGEXP_LIKE (string expression, match_pattern, [matching parameter] );

The REGEX_LIKE function is has following options:

String Expression:

String Expression is any string from which user needs to search patterns.

Match_Pattern:

This is nothing but regular expression matching pattern.

Matching parameter:

Matching parameter is used for changing behavior of regular expression .Means if user wants to check the matching pattern is case sensitive then need to use the matching parameter. Matching parameter is optional in REGEXP_LIKE function.

Regexp_Like Examples with different options:

The REGEXP_LIKE function is used to find the matching pattern from the specific string. Let us create a table named Employee and add some values in the table.

Example 1: User wants to fetch the records, which contains letter ‘J’.

The above scenario will be achieved by using REGEXP_LIKE function.

SELECT *

FROM Employee WHERE regexp_like (name, ‘J’);

 

The above statement will fetch all the records from Employee table where name contains ‘J’ letter.

Output :

Name Salary
Jayram 78000
Raj 85000
Jitu 70000

 

Example 2: User wants to fetch the records, which contains letter ‘mi’.

To achieve the above functionality user needs to use REGEXP_LIKE function.

SELECT *

FROM Employee WHERE regexp_like (name, ‘mi’);

 

The output of the above statement is following:

Output :

Name Salary
Amit 78000
Sumit 85000
Mit 70000

 

Added Knowledge :

REGEXP_LIKE operator is used by different cards for checking validations.Following are the formats of different cards.

Visa credit card:

4[0-9]{3}\s[0-9]{4}\s[0-9]{4}\s[0-9]{4}

MasterCard:

5[0-9]{3}\s[0-9]{4}\s[0-9]{4}\s[0-9]{4}

American Express:

37[0-9]{2}\s[0-9]{4}\s[0-9]{4}\s[0-9]{4}

Discover:
6011\s[0-9]{4}\s[0-9]{4}\s[0-9]{4}

 

 Example 3 : Match one or more than one patterns using Pipe | Operator of  REGEXP_LIKE Examples

One of the most important scenario is using pipe operator. The pipe operator is used to specify alternative matches. There are requirement where user needs to fetch records of two specific sequences then Pipe operator is useful in that case.

If user wants to fetch records of pattern like ‘mi’ and ‘mee’ from Employee table.

SELECT *

FROM Employee WHERE regexp_like (name, ‘mi|mee’);

 

Output :

Name Salary
Amit 78000
Sumit 85000
Mit 70000
Sumeet 71000

 

Example 4: Pipe Operator Complex REGEXP_LIKE Examples

The complex pattern is achieved using pipe operator. There are some scenarios where user does not know the actual spelling of the name. These kind of scenarios can be achieved using Pipe operator in REGEXP_LIKE operator.

If user does not know the spelling of amit whether it is Amit or Ameet.

SELECT *

FROM Employee WHERE regexp_like (name, ‘A(mi|mee)t’);

 

Output :

Name Salary
Amit 78000
Ameet 85000

 

Example 5: Case Sensitive Search

User can use the third argument as ‘c’ to match the case sensitive pattern. User can use ‘i’ option to match case insensitive search.

The following example will help user to search the string which contains ‘mi’ and ‘mee’ in small letters.

SELECT *

FROM Employee WHERE regexp_like (name, ‘mi|mee’,’c’);

 Output:

Name Salary
Amit 78000
Sumit 85000
Sumeet 71000

 

The following sql statement is used to fetch the case insensitive records.

SELECT *

FROM Employee WHERE regexp_like (name, ‘mi|mee’,’i’);

 Output :

Name Salary
Amit 78000
Sumit 85000
Mit 70000
Sumeet 71000

 

Example 6:using ^ Caret operator REGEXP_LIKE Examples

The ^ Caret operator is used to indicate the beginning of the string.There are some scenarios where user needs the records starts with some specific pattern then caret operator is useful.

The following statement will fetch all employees starts with Am and Su then following statement is used:

SELECT *

FROM Employee WHERE regexp_like (name, ‘^Am|^Su’,’c’);

 Output :

Name Salary
Amit 78000
Sumit 85000
Sumeet 71000

 

Example 7 :  Example using $ operator

The $ operator is used to indicate the end of the line of the string.There are some schenarios where user needs to find the records which ends with specific pattern then $ operator is useful.

The following statement will fetch all employees which end with ‘it’  or ‘et’ pattern.

SELECT *

FROM Employee WHERE regexp_like (name, ‘it$|et$’,’c’);

 Output : 

Name Salary
Amit 78000
Sumit 85000
Sumeet 71000

 Example 8 :  Using square bracket.

The square brackets are used to specify the matching list that should match any one of the expression.If user wants to fetch the records such as the records contains Y and J.

SELECT  *

FROM Employee WHERE regexp_like (name, ‘[YJ]’);

 

Output :

Name Salary
P Jayram 78000
R Yash 85000

 

The above query will return all employees where name contains ‘Y’ or ‘J’.

Example 9: Use of square bracket for case sensitive search.

If user wants to fetch records where name contains ‘Y’ or ‘j’.

SELECT *

FROM Employee WHERE regexp_like (name, ‘[Yj]’);

Output :

Name Salary
R Yash 85000

 

The above query will return the Employee names contains ‘Y’ in capital and ‘j’ in smallcase.

Example 10 : Use of Brackets with – (dash) operator.

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’.

SELECT *

FROM Employee

WHERE regexp_like (name , ‘[d-g]a’) ;

Output :

Name Salary
Vargas 85000
Baida 50000

REGEXP_LIKE Examples

Example 11: Using period (.) Operator

The operator (.) called as period operator. The period operator matches any character except null. 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’.

SELECT *

FROM Employee

WHERE regexp_like (name , ‘[b-g].[a]’) ;

Output :

Name Salary
Sarchand 85000
Abha 50000

Example 11: Use of Curly braces

The number in between curly braces indicates the number of occurrences.

If user wants the records where the employee which has pattern like ‘ee’.

SELECT *

FROM Employee

WHERE regexp_like (name , ‘[e]{2}’) ;

Output :

Name Salary
Ameet 85000
Sonalee 50000

Example 12:Email validation using REGEXP_LIKE

 The best example of REGEXP_LIKE function is email validation.Lot of programmers use the E-mail validation function.But with using REGEXP_LIKE function user can achieve the email validation using one line.

 SELECT
   Email
FROM
   Employee
where NOT REGEXP_LIKE(Email, ‘[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}’, ‘i’);

The above Select statement used for e-mail validation.

Example 13 : Telephone number mask

The another best use of regular expression is telephone number mask.Following select statement is used for telephone number mask:

ALTER TABLE

Employee

ADD (

CONSTRAINT

phone_number_format

CHECK

(REGEXP_LIKE(Employee_phone,

‘^\([[:digit:]]{3}\) [[:digit:]]{3}-[[:digit:]]{4}$’)));

he above statement will create constraint of telephone number.Hope everyone like this article on REGEXP_LIKE Examples.Please comment in comment section if any query or information needed.

25 Replies to “Regexp_like Examples | How to use Regexp_like in SQL?”

  1. Hi Amit,

    What is the easiet query to search entire oracle database for the data in the table contains Name, email etc?

    1. Hello,

      The easiest query to search entire oracle database is

      Select table_name from all_tab_columns where column_name in (‘Email’,’Name’);

      Regards and thanks,
      Amit S

  2. Hi Amit,

    Nicely explained. I have the below requirement, please help me in achieving the same:

    Below are 2 statements wherein Statement1 should return a value but Statement2 should not. In my case, both are returning the values which is incorrect:
    STATEMENT1:
    SELECT ‘HELLO’ FROM DUAL WHERE ‘THIS IS VENTURE COMMERCE FROM INDIA’ LIKE ‘%VENTURE COMMERCE%’;
    STATEMENT2:
    SELECT ‘HELLO’ FROM DUAL WHERE ‘THIS IS VENTURE COMMERCE FROM INDIA’ LIKE ‘%E COMMERCE%’;

    How can I achieve the above requirement?

    Thanks & Regards
    Saleem Javed

  3. it is meant to be exact digits as mention in {} brackets.
    (REGEXP_LIKE(Employee_phone,

    ‘^\([[:digit:]]{3}\) [[:digit:]]{3}-[[:digit:]]{4}$’)));

    But when I tried it seemed minimum of that number of digits.
    like [[:digit:]]{3} mean min 3 digits but not exactly digits. is that so?

  4. I want to be able to search for names, first name and last name in the same field. The names can also be last name + first name. Sometimes I know only first names (and there can be several in different order)
    So, is it possible to search for first name AND second name and get result even if they are in reversed order in the field Im searching?

  5. What if I want to check for several things in a field?

    If I want to check if there are any BMWs in the field CarType , OR if there are any Volvos I write
    REGEXP_LIKE ( CarType, pattern , ‘Volvo|BMW’)

    But if I want Volvos AND BMWs?

  6. I have a string pattern with ‘IND|Sales|Automative’ in a column and ‘IND|Fin|Accounts’.

    So how can I use REGEXP_LIKE(‘IND|Sales|Automative| IND|Fin|Accounts’), if use like this iam getting wrong results.

    The problem is | exists in my string value

  7. Hello Amit!
    Thank you for explaining REGEXP_LIKE function.
    I need to validate address field and the only allowed characters for this field are characters(a-z), numbers and spaces. I want do retrieve all rows where the address field has any characters other than a-z(both upper case and lowercase), numbers and spaces. I am using the below SQL in Oracle but it is not working.
    select * from table_address where 1=1 and NOT REGEXP_LIKE(ADDRESS, ‘[a-z0-9 ]’, ‘i’)
    I inserted a value “123&%/\| street” in one of the test rows but the above query is not returning that row.
    Thanks in advance

  8. Hi Amit,

    If I want to search entire DB for tables containing special characters . How would I do that?
    I cannot pass columns one by one to the REGEXP_LIKE.
    Please suggest.

    1. Hi Milind,

      You can use system tables.

      Example : In oracle you can use regexp_like with All_Tables system table ..You can use table_name column with Regexp_Like.

      Regards,
      Amit S

  9. Hey,
    Great article. I had a question. I have table with 1000s of records. But I want to fetch only those records whose title has this exact sentence ‘ppcn minutes payment’ . Doesn’t matter what text is before or after this sentence in the title. Is this achievable?

Comments are closed.