Menu
I trying to use Postgres regexp_matches function to pull hashtags out of a string... The following example returns only a match - how do I extract both hashtags?
regexp_matches("Hello #world #planet", '#([A-Za-z0-9]+)')
Cheers, Andrei
675
The output contains the ‘#eduCBA’ and ‘#PostgreSQL’ string, which means matching strings with regular expression defined in pattern. In the above example, the regular expression is “ ( [A-Za-z0-9]+)”, which matches the string, which begins with a character hash (#) and is followed by any alphanumeric characters.
The PostgreSQL REGEXP_MATCHES () function matches a regular expression against a string and returns matched substrings. The following illustrates the syntax of the PostgreSQL REGEXP_MATCHES () function: The source is a string that you want to extract substrings that match a regular expression.
The PostgreSQL Regex function returns no row, one row, or multiple rows per defined pattern. The PostgreSQL Regex function may return zero, one, or several rows depending on the pattern specified. The PostgreSQL Regex functions allow us to search for any word in the needed string at any position, or we can search for simply the first occurrence.
There are three separate approaches to pattern matching provided by PostgreSQL: the traditional SQL LIKE operator, the more recent SIMILAR TO operator (added in SQL:1999), and POSIX-style regular expressions.
You should enclose string literal with ' not ". Adding 'g' as proposed in comment should help:
SELECT regexp_matches('Hello #world #planet', '#([A-Za-z0-9]+)', 'g')
SqlFiddleDemo
╔════════════════╗
║ regexp_matches ║
╠════════════════╣
║ world ║
║ planet ║
╚════════════════╝
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
