Solved

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

Posted on 2011-03-06
6
300 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL inner join confusion 15 42
the whoisactive update 12 39
Sql query on a varchar that is numeric. 8 24
Getting same value for every field in SQL 2 21
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.

914 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now