mysql通过表计数主题标签的数量

Question

i have mysql table text(id, text) with values:

1, 'some #text with #hashtags ...'
2, 'more #text without....'
... and more

is possible in sql get count for any hashtags ? something

like select count(regexp) from text where...regexp... magic happened ;)

needed result: #text ...2x, $#hashtags 1x

i cant select text with

select * from  table where text REGEXP '#[a-z]*

but this way i get only rows with any #hashtag

Any idea ?

Ok, i can select all and count in result with php of course, but its possible only mysql solution ?

Answer 1

1. Select rows from MySQL SELECT text FROM text WHERE text REGEXP '#(\w+)'

\w means Any word character (letter, number, underscore)

+ means one or more

1. Get hashtag from text column using the preg_match with the same pattern '#(\w+)' and using the $match (take a look at the reference!)

$match =

Array
(
    [0] => #hashtag
    [1] => hashtag
)

1. Count in array, $hashtags_count[$match[1]]++