Solved

how to count rows which match various words, broken  down by month

Posted on 2011-03-06
6
301 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:timbo007
  • 3
  • 3
6 Comments
 
LVL 15

Expert Comment

by:derekkromm
ID: 35048835
select month(txttime), year(txttime), count(*) from txts
where (message like '%bully%' or message like '%bullied%' or message like '%blly%')
group by month(txttime), year(txttime)
0
 

Author Comment

by:timbo007
ID: 35050914
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...
0
 
LVL 15

Accepted Solution

by:
derekkromm earned 500 total points
ID: 35050995
well, it'll find what you tell it to find ;)

is there anything else i can help with?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:timbo007
ID: 35051055
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
0
 
LVL 15

Expert Comment

by:derekkromm
ID: 35051153
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/

0
 

Author Closing Comment

by:timbo007
ID: 35052334
Thanks, could be explained with better examples though but I can figure the rest now out cheers :)
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQl Query to find x consecutive Nbrs in a Table 30 95
T-SQL: Do I need CLUSTERED here? 13 59
two ways encryption with php 3 37
How to fix Datetime in MySQL? 4 47
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question