How to Use regexp_like in PostgreSQL?

PostgreSQL database is known for its powerful processing capabilities and one such feature is the ability to use regular expressions (RegEx) to search, filter, and validate data. RegEx allows developers to search for specific patterns in strings, making it useful for the validation of data manipulation of strings and filtering out data.

The regexp_like() utilizes the functionality of regex in PostgreSQL. This article will provide an in-depth understanding of how to use the regexp_like() function in PostgreSQL, including its syntax, functionality, and real-world examples.

What is regexp_like in PostgreSQL?

The regexp_like() function in PostgreSQL is used to match the string with a specific regular expression pattern. This function returns a Boolean value (TRUE or FALSE), depending on whether the string matches the specified pattern. This function is very helpful in checking if the input string confirms a specific pattern/format.

It is similar to other regex functions like regex_replace, regexp_matches, and regexp_split_to_table. However this regexp_like() is specifically for evaluating whether there exists a match between the provided string and given the regular expression pattern or not.

Syntax:

regexp_like(string, pattern , flags)

Key terms:

• string : It is the input string that need to be matched or evaluated.
  
• pattern : it is the regular expression pattern to match the input string.
  
• flags(optional) : flags modifies the behavior of the regular expression , including case-insensitive matching or multi-line support matching of string, etc.

Commonly Used Flags

• i - case insensitive matching of input string.
  
• g - global matching ( find all the matches rather than stopping after the first ).
  
• m - multi-line mode.

Examples of Using regexp_like() in PostgreSQL

Here are several practical examples that demonstrate the usage of regexp_like() in different scenarios. These examples will illustrate how to effectively use this function to filter and validate data in PostgreSQL, improving the overall efficiency of our queries.

Query:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    department VARCHAR(50)
);

INSERT INTO employees (name, email, department) 
VALUES 
('Jonny Doe', '[email protected]', 'HR'),
('Jane Smith', '[email protected]', 'Engineer'),
('Mohit Sigh', '[email protected]', 'Finance'),
('Sara Sharma', '[email protected]', 'Marketing');

Output:

Example 1: Check if a string contains digit or not

This example shows how to use regexp_like() to check whether an input string contains any numeric digits. By using the regular expression [0-9], we can search for any numbers within the string, making this a useful function for data validation or filtering out entries that include numerical values.

Query:

SELECT  regexp_like( 'abc123'  ,  '[0-9]' );

Output

TRUE

Explanation :

The input string "abc123" contains digits "123" which satisfies the regular expression and the regexp_like() functions then return TRUE in response to it.

Example 2: Case-insensitive Matching

In this example, we explain how to perform case-insensitive matching using the regexp_like() function. This can be useful when we need to match strings regardless of their letter case, such as checking for text in user inputs or names. By applying the 'i' flag, we ensure that both uppercase and lowercase characters are treated equally.

Query:

SELECT  regexp_like ( "HelloWorld" , 'helloworld' , 'i' );

Output

TRUE

Explanation :

The input string "HelloWorld" matches with the 'helloworld' because we are using the flag 'i' for case-insensitive matching, hence true is returned.

Example 3: Find Emails with ".com" Domain

This example demonstrates how to use the regexp_like() function to find all employees whose email addresses contain the ".com" domain. This is particularly useful when filtering out specific email domains from a dataset, such as identifying corporate or specific domain email addresses.

Query:

SELECT   *  FROM   employees
WHERE  regexp_like ( email ,  '@.*\.com$');

Output

Explanation:

• @.*\.com$ : this pattern matches all the string that starts with @, followed by number of characters (.*), and ends with .com (.\com$).

Example 4: Find Names Starting with "J" and Containing a Space

In this example, we use the regexp_like() function to search for employee names that start with the letter "J" and include a space followed by another word. This can be useful for identifying names that match a specific pattern, such as first and last names that begin with certain letters.

Query :

SELECT   *   FROM   employees
WHERE   regexp_like ( name  ,  '^J\w*\s\w+' );

Output

Explanation:

The regular expression ^J\w*\s\w+ matches names that start with "J", followed by any number of letters or digits (\w*), a space (\s), and at least one more word (\w+).

Conclusion

The regexp_like() function in PostgreSQL is very powerful way of matching a string using regular expressions, allowing developers to efficiently perform pattern matching, validation, and filtering of string data. With its simple syntax and the ability to use optional flags like case-insensitivity and global matching, regexp_like() can handle a variety of use cases.