In PostgreSQL, the regexp_matches() function matches a regular expression in a specified main string. This function takes in the string and the regular expression to be matched as an argument along with an optional flag and returns the first matched substring from the main string.

Let’s get into the details of the regexp_matches() function.

What Does PostgreSQL regexp_matches() Function Do?

The regexp_matches() function basically returns the result of the first matching of the regular expression against the main string. The basic syntax of the regexp_matches() function is given below:

regexp_matches(Main_String, Regexp[, flags]);

The regexp_match() function takes in 3 arguments:

● The first parameter is the main string in which the search will take place.

● The second argument is a POSIX regular expression that needs to be searched in the main string.

● The third argument, which is completely optional, is the flag. The flag is responsible for controlling the behavior of the function for example; the ”i” flag makes a matching case-insensitively. The “g” flag will return all the matching substrings from the main string.

The function will always return a set of string values. These values correspond to the matches of the expression in the main string. If the match does not exist, the result will be NULL.

Let’s move towards an example to make it more clear.

Example

If we want to match an expression from a string, we will write the query as:

SELECT regexp_matches('bats Eaten gate Atone', 'at.');

In the above query:

● The main string is "bats Eaten gate Atone" in which the regular expression is going to be matched.

● The expression we want to search for is "at.". You can see the regular expression contains a dot “.”, we can use a dot or multiple dots after and before the regular expression. The number of dots and the placement of dots simply specify the number of characters before or after the expression in the returned text.

In case of one dot after the expression "at", the query will search for the first string in the main string and will return the array of the string containing the first “at” and an additional character from the matched string after the “at”.

The output will look like this:

Let's add another dot before ”at” and see the results:

SELECT regexp_matches('bats Eaten gate Atone', '.at.');

The query will look for the first “at” match in the main string and will result in the string having a character before and after the matched string in the main string. The output is:

You can see that the query matched the “at” expression with the string and gets the first match. The dot before and after the expression will join a character before and after the expression from the string.

In the next example, let’s use the flag ”i”. The “i” flag does the matching case insensitively. Consider the below query:

SELECT regexp_matches('bats Eaten gate Atone', '.TO.', 'i');

So the query will search for the ”TO” expression being insensitive to the case. The result will be:

There is another flag that is usually used i,e, “g” flag, or global flag. This flag returns all the matching strings from the main string. Let's use it in a query:

SELECT regexp_matches('bats Eaten   gate Atone', '.at.', 'g');

The output will contain all the strings having the expression “at”.

We can also use a combination of two flags as well. Such as sometimes a combination of the “i” and “g” flags is used.

SELECT regexp_matches('bats Eaten   gate Atone', 'AT.', 'ig');

The above query will do case-insensitive matching and will result in all the matching results as output.

The regexp_matches() function is more flexible than the regexp_match() function because we can get the first match as well as all the matches, which the regexp_match() does not provide. So this was it about the regexp_matches() function.

Conclusion

This function matches a specified expression against a specified main string and returns the set to strings/characters that match first. We can get all the match results by using a flag “g” with the function. These flags are completely optional but if added will give some specific results for the function. In this article, we have discussed the regexp_matches() function in detail.