How REGEXP_REPLACE() Works in PostgreSQL

In PostgreSQL, we can use the regexp_replace() function to replace a substring within a given string, based on a given POSIX regular expression. We can specify that all matches are replaced or just the first match.

We pass the string as the first argument, the pattern as the second, and the replacement text as the third argument. We also have the option of specifying the start position as the fourth argument, and we can specify a flag to determine how the function behaves.

Continue reading

Using REGEXP_SPLIT_TO_ARRAY() in PostgreSQL

In PostgreSQL, the regexp_split_to_array() function splits a string using a POSIX regular expression as the delimiter, and returns the result in a text array.

So we use a POSIX regular expression to specify the delimiter/s, and split the string based on that.

We pass the string as the first argument and the pattern as the second. We can also specify a flag to determine how the function behaves.

Continue reading

How REGEXP_SPLIT_TO_TABLE() Works in PostgreSQL

In PostgreSQL, the regexp_split_to_table() function splits a string using a POSIX regular expression as the delimiter, and returns the result in a table.

So we use a POSIX regular expression to specify the delimiter/s, and split the string based on that.

We pass the string as the first argument and the pattern as the second. We can also specify a flag to change the behaviour of the function.

Continue reading

Understanding PostgreSQL’s REGEXP_MATCHES() Function

In PostgreSQL, the regexp_matches() function returns substrings that match a given POSIX regular expression in a given string. We can specify that all matches are returned or just the first match. If all matches are returned, each is returned in a separate row.

We pass the string as the first argument and the pattern as the second argument. We can also provide a flag as an optional third argument, which determines how the function behaves.

Each returned row is a text array containing the whole matched substring or the substrings matching parenthesised subexpressions of the pattern.

Continue reading

How REGEXP_MATCH() Works in PostgreSQL

In PostgreSQL, the regexp_match() function returns a text array containing substrings that match a given POSIX regular expression in a given string. Only the first match is returned (to return all matches, use regexp_matches() instead).

We pass the string as the first argument and the pattern as the second argument. We can also provide a flag as an optional third argument, which determines how the function behaves.

Continue reading

A Quick Look at PostgreSQL’s REGEXP_LIKE() Function

In PostgreSQL, we can use the regexp_like() function to check whether or not a match of a POSIX regular expression occurs within a given string.

We pass the string as the first argument and the pattern as the second argument. We can also provide a flag as an optional third argument, which determines how the function behaves.

Continue reading

Understanding PostgreSQL’s REGEXP_INSTR() Function

In PostgreSQL, the regexp_instr() function returns the starting or ending position of the N‘th match of a POSIX regular expression pattern to a string. If there’s no match, it returns zero.

We pass the string and pattern as arguments. The function also accepts some optional arguments that allow us to be specific with how the function works.

Continue reading

A Quick Look at PostgreSQL’s REGEXP_COUNT() Function

In PostgreSQL, the regexp_count() function returns the number of times a given POSIX regular expression pattern matches in a given string.

We pass the string and pattern as arguments. We can also pass an argument to specify where to start the search. Additionally, we also have the option of specifying a flag that changes the function’s behaviour.

Continue reading