how to count rows which match various words, broken down by month
I have a table 'txts' with columns like 'message' (255 varchar) 'txttime' (datetime) and I would like to count all the rows which 'message' contains any of the words or phrases 'bully, bullied, blly' and want to know how many times they appeared in a given daterange 'txttime' but broken down into monthly occurrence ...
Seems straight forward enough but I can't figure it out.
The dataset contains 350000 rows per year of data.
Seems straight forward enough but I can't figure it out.
The dataset contains 350000 rows per year of data.
ASKER
Thanks, but this captures other words which have these letters in them too, I guess most of the time it would have spaces around words but sometimes it could be at the beginning or end of the data or also it could have a ',' or a '.' I guess, the data is TXT messages that you would send with your cell phone.. I have a list of about 100 different sets of these key words I am running in a loop...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
yes, can you please tell me how to get those words as the result, I don't think using like '%word%' is good enough, how can it know if there are full stops or at the beginning etc? would using a regex be better? though this is going over 350000 rows so I guess regex would be slow, I am happy to test it though but don't know the syntax
at the beginning and end you'd have to do:
left(message, 5) = 'bully' or left(message, 4) = 'blly'
or right(message, 5) = 'bully' or right(message, 4) = 'blly'
etc
if you want to check for spaces, you'd have to do message like '% bully %'
using regex (assuming you're using mysql): http://www.tech-recipes.com/rx/484/use-regular-expressions-in-mysql-select-statements/
left(message, 5) = 'bully' or left(message, 4) = 'blly'
or right(message, 5) = 'bully' or right(message, 4) = 'blly'
etc
if you want to check for spaces, you'd have to do message like '% bully %'
using regex (assuming you're using mysql): http://www.tech-recipes.com/rx/484/use-regular-expressions-in-mysql-select-statements/
ASKER
Thanks, could be explained with better examples though but I can figure the rest now out cheers :)
where (message like '%bully%' or message like '%bullied%' or message like '%blly%')
group by month(txttime), year(txttime)