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
– Varun B. Krishnan, Commented Feb 19, 2017 at 11:05

bettersayhello · Accepted Answer · 2017-02-19 15:43:33Z

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, 2017 at 15:43

bettersayhello

2,5994 gold badges33 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Darshan Mehta · Accepted Answer · 2017-02-19 11:20:02Z

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, 2017 at 11:20

Darshan Mehta

31k14 gold badges75 silver badges111 bronze badges

1 Comment

Varun B. Krishnan Over a year ago

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.

Collectives™ on Stack Overflow

count multiple occurence substrings in MySql

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related