How to Use Regex in PostgreSQL - LIKE and ~ Tilde

Introduction

A regular expression is a set of characters in a specific sequence that helps identify the required correct input. We are going to take a look at regex and how to use them using different approaches or operators such as LIKE, NOT LIKE, and SIMILAR TO in addition to the tilde operator family such as ~, ~, !~, !~ which matches regular expressions in case sensitive and case insensitive situations. LIKE and SIMILAR TO operators perform more or less the same functionality. Now let’s discuss these operators and let’s see how to use them as part of regex.

LIKE Operator in Regex

The LIKE operator returns a true result if the input string matches with the pattern. NOT LIKE would only return true if LIKE operator returns false and it would only return false if LIKE operator returns true.

Working syntax of LIKE and NOT LIKE is as follows:

Let’s do some practical examples now:

In the above examples you see, the literal pattern, without percentage and underscore signs, means the string itself would be considered as a pattern and it’s working like and equal sign and matches input letter by letter. Underscore (_) in above example basically looks for only single character match and percentage (%) looks for none, single or multiple characters match. LIKE operator works best with full strings and if a substring or a small part within a string needs to be matched then in that case the pattern must contain (%) at the beginning of substring and at the end of substring as well.

Tilde Regular Expressions (~)

Tilde regular expressions are more powerful in comparison to LIKE and SIMILAR TO operators. Tilde (~) operator helps the regular expression to be matched in any part of the string easily. Following are the operators that can be used in this category:

• (~) It matches the regular expression with a case sensitive approach
• (~*) It also matches the regular expression but with a case insensitive approach
• (!~) It does not match a regular expression with a case sensitive approach
• (!~*) It does not match a regular expression with a case insensitive approach

Some examples related to above tilde operators are given as below:

Tilde is the part of POSIX regular expressions and it also contains different functions within. There is a substring function which helps us identify and pick up the substring that matches the POSIX regular expression. The return value would be the substring or the text that has matched with the regular expression, if there is no match then it would return a null value. Parentheses must be used with great care in regular expressions and in substring function as well. We have shown the right usage of parentheses in the example given below, if you use any other way of putting parentheses in regular expressions then it would cause an exception such as the part of the substring that would match the initial subexpression, which is enclosed in parentheses, would be returned. The substring function examples:

Conclusion

There are other multiple POSIX functions as well such as regexp_matches(), regexp_split_to_table(), regexp_split_to_array() etc. They can help you get most out of regular expressions in PostgreSQL. If we try to jump in more detail of regular expressions then we have Atoms, Quantifiers, Constraints, Bracket expressions, escapes, back references along with regular expression meta syntax and specific regular expression matching rules which are pre-defined.

Certainly, there are several different types and details of regular expressions which cannot be covered in a single piece of content but we will attempt to cover all of them in the near future via our future content. Stay tuned!