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

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.