PostgreSQL – REGEXP_MATCHES Function
Syntax:REGEXP_MATCHES(source_string, pattern [, flags])
Let’s analyze the above syntax:
- The source is a string from which the regular expression matches and returns the substring.
- The pattern is a POSIX regular expression for matching the source string.
- The flags argument handles the function when more than one character matches the pattern.
- The REGEXP_MATCHES() function returns the queried string based on the matches.
Suppose, you have a social networking’s post as follows:
'Learning #Geeksforgeeks #geekPower'
The following statement allows you to extract the hashtags such as Geeksforgeeks and geekPower:
SELECT REGEXP_MATCHES('Learning #Geeksforgeeks #geekPower', '#([A-Za-z0-9_]+)', 'g');
This is common for all patterns that can be matched using the Regular Expressions as shown in the below example:
SELECT REGEXP_MATCHES('ABC', '^(A)(..)$', 'g');