mysql 子串出现的次数,计算MySQL列中子字符串的出现次数

I have a table which stores information of a lot of twitter tweets including the tweet text and the screen name of the user who tweeted the tweet. The tweets contain hashtags (starting with #), I want to count the number of hashtags that a specific user has tweeted:

tweet_id | tweet_text | screen_name |

--------------------------------------------------------------------------------------------

1 | #hashtag1 #otherhashtag2 #hashtag3 some more text | tweeter_user_1 |

2 | some text #hashtag1 #hashtag4 more text | tweeter_user_2 |

3 | #hashtag5 #hashtag1 @not a hashtag some#nothashtag | tweeter_user_1 |

4 | #hashtag1 with more text | tweeter_user_3 |

5 | #otherhashtag2 #hashtag3,#hashtag4 more text | tweeter_user_1 |

If I were to count the hashtags of tweeter_user_1, the result i expect is 8, if i wanted the hashtags of tweeter_user_3 it should return 1. How can I do it assuming that my table name is tweets.

I tried this: SELECT COUNT( * ) FROM tweets WHERE( LENGTH( REPLACE( tweet_text, '#%', '@') = 0 ) ) AND screen_name = 'tweeter_user_1' but it didn't work

I would be happy if the result of tweeter_user_1 was 9 too :D

解决方案

This should give you a list of screen_names and the total count of all hashtags they use.

SELECT foo.screen_name, SUM(foo.counts) FROM

(

SELECT screen_name,

LENGTH( tweet_text) - LENGTH(REPLACE(tweet_text, '#', '')) AS counts

FROM tweet_table

) as foo

GROUP BY foo.screen_name

But.... it's a nasty query if the table is huge. I might specify a specific users in the inner select if you just need counts for a single user. Like this:

SELECT foo.screen_name, SUM(foo.counts) FROM

(

SELECT screen_name,

LENGTH( tweet_text) - LENGTH(REPLACE(tweet_text, '#', '')) AS counts

FROM tweet_table WHERE screen_name = 'tweeter_user_1'

) as foo

GROUP BY foo.screen_name