Extracting matches of REGEXP in SQL

Asked Oct 14 '15 at 13:22

Active Oct 14 '15 at 13:22

Viewed 112 times

I have a table of tweets stored in the body field of the table. I run the following SQL query to check whether the tweet has an @mention:

SELECT username, created, body FROM mytable WHERE body REGEXP '@([a-zA-Z0-9\_\.]+)' ;

It works, and it gets me the rows that match the REGEXP. Is it possible to just return the @mentions in the body rather than the entire body (same as the python re.findall() function? The table is big and doing analysis on all of it is taking too long, and all I really need are the @mentions from each body field.

asked Oct 14 '15 at 13:22

David

1,454
3
16
27

1

This is similiar to: http://stackoverflow.com/questions/4021507/mysql-use-regex-to-extract-string. – Dan Oct 14 '15 at 13:27
do you need to count the occurrences of `@mention` in each row? – Vamsi Prabhala Oct 14 '15 at 13:42
No, I just need the name(s) of the `@mention` in each row – David Oct 14 '15 at 14:01
There is no simple way to extract a part of `body` based on a regexp. – Rick James Oct 16 '15 at 01:51

Extracting matches of REGEXP in SQL

0 Answers0