count multiple occurence substrings in MySql

Question

I'm pulling data from Twitter API into my DB. There is a column 'hashtags' which stores a list of hashtags used in the tweet.

Table name: brexittweets
Column: hashtags varchar(500)

I want to count the number of hashtags. For example

                 Hashtags
Tweet1: ['EUref', 'Brexit', 'poll']

Tweet2: ['Brexit', 'Blair']

Tweet3: ['Brexit', 'Blair', 'EUref']

Result should be:

hashtag     count(hashtag)
Brexit      3
EUref       2
Blair       2
poll        1

What I was thinking of doing: Tried to take substring between quotes ' ', but it occurs multiple times in the same row.

Tried using strpos to find instances of ' ', but it returns only the first instance.

Is there a way to do this with queries? I was thinking of trying out a procedure, but it gets complicated because I need to print these results on a web page using PHP.

Yes. That's the way it's writing from Python, but I can get rid of them if they're too problematic. — Varun B. Krishnan, Feb 19 '17 at 11:05

score 1 · Answer 1 · answered Feb 19 '17 at 15:43

1

If only you've normalized your table such that each tag in a tweet gets stored on its own row, your problem would be solved easily by using COUNT with GROUP BY.

answered Feb 19 '17 at 15:43

ultrajohn

2,527
4
31
56

score 0 · Answer 2 · answered Feb 19 '17 at 11:20

0

Assuming all the tags are separated by ', ', you can do the following:

SELECT 
    hashtags, 
    ROUND (   
        (
            LENGTH(hashtags)
            - LENGTH( REPLACE ( hashtags , "', '", "") ) 
        ) / 4) + 1
     AS count
    from brexittweets

Here's the SQL Fiddle.

answered Feb 19 '17 at 11:20

Darshan Mehta

30,102
11
68
102

Thank you. But does not work, because this just returns the number of hashtags in each record. I need count of each hashtag in the database. So for instance 'poll' should return a count of 1, which this query does not. – Varun B. Krishnan Feb 19 '17 at 11:27

count multiple occurence substrings in MySql

2 Answers2