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

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

How Does PostgreSQL regexp_match() Function Do?

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

regexp_match(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. But note that the regexp_match() function does not support the “g” flag. A list of flags is given in the Postgres documentation.

The function returns 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 have a look at 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_match('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_match('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_match('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 in the regexp_matches() function but is not supported by the regexp_match() function i,e, “g” flag, or a global flag. Let’s see how the query works if we have the flag “g”.

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

The output will throw an error.

The error says that we can not use the “g” flag with the regexp_match() functions this flag is not supported by the function. Remember that the regexp_matches() function is more flexible than the regexp_match() because it only gives the first match result while the regexp_matches() can return first as well as all the matching results using the “g” flag.

So this was all about the regexp_matches() function.

Conclusion

The regexp_match() function matches a specified expression against a specified main string and returns the set to strings/characters that match first. We can not use the “g” with the function as we used it in regexp_.matches() function. The flags are completely optional but if added will give some specific results for the function. cIn this article, we have discussed the regexp_match() function in detail.