| Author |
 Topic  |
|
dayve
Forum Moderator
USA
5820 Posts |
 Posted - 31 December 2003 : 22:17:55
|
What is the best way to get a total of a specific keyword that exists in a database field? I don't want to count how many records have the true condition, I want to count how many times the condition exists. For example:
MyField
+==============+
1|hello to you |
2|hello hello |
3|you said hello|
+==============+
I want to get a count of the word "hello" and the result should be 4. TIA. |
 |
Edited by - dayve on 01 January 2004 01:34:05 |
|
|
laser
Advanced Member
Australia
3859 Posts |
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 01 January 2004 : 05:28:51
|
| I'm pretty sure this can be done using an SQL statement. Using Count and Groupting with a nested Select perhaps. |
|
 |
|
|
redbrad0
Advanced Member
USA
3725 Posts |
|
|
snaayk
Senior Member
USA
1061 Posts |
Posted - 02 January 2004 : 02:27:18
|
| I've been racking my brain on this one. I don't think it can be done with SQL. You could create a sproc that would handle it nicely in MSSQL, or a looping function in asp. |
|
|
|
redbrad0
Advanced Member
USA
3725 Posts |
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 05 January 2004 : 18:35:16
|
| I found something at 4guysfromrolla I can use. Not completely happy with the way I have to do it, but it will definately work. I'd post a link to it, but I am at work and don't have it readily available and it was a pain to find. However, when I get home tonight I will post a link to it. All you're really doing is splitting a string using the word as a delimiter and counting instances of it. |
|
|
|
|
dayve
Forum Moderator
USA
5820 Posts |
|
|
redbrad0
Advanced Member
USA
3725 Posts |
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 05 January 2004 : 23:34:57
|
quote:
Originally posted by redbrad0
but will this let you order by the number of times its entered into each field in the DB?
sure, when I loop it through a recordset. |
|
|
|
|
snaayk
Senior Member
USA
1061 Posts |
Posted - 05 January 2004 : 23:41:10
|
Oh :( I was attempting to find a way that it could be done with SQL, in the query that is. In a sproc or in ASP it should be an easy matter.
Why are you not happy with the solution you found? |
|
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 06 January 2004 : 00:21:16
|
| I am not happy with the way I found because I just want to pull these stats directly from the database in the analyzer and not have to run any scripts. I thought maybe a nested/aggregate sql statement would do the trick, but alas, I am not getting the results I want so the info I found at 4guys will do. |
|
|
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 06 January 2004 : 11:36:04
|
Probably not a much better solution, but you could try going through the string one character at a time and checking if the next x characters equal the keyword increment a variable by 1. Something like (using your original example):Dim strKeyword, strFieldValue, intCount, x
Dim strKeyword = "hello"
Dim strFieldValue = "hello to you hello hello you said hello"
intCount = 0
for x = 0 to len(strFieldValue)
if mid(strFieldValue,x,len(strKeyword)) = strKeyword then intCount = intCount + 1
next As I said, not a perfect solution, especially if you're looping through long strings and/or have a lot of records to check, but worth a suggestion.
|
 Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
|
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 06 January 2004 : 13:59:33
|
| thanks for the suggestion, but that method would be way too intensive on a large database. Keep in mind I am checking every topic, every reply, just as if I was searching for a word. I have 140,000+ topics so checking character by character would more than likely timeout. |
|
|
|
|
grazman
Junior Member
USA
193 Posts |
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 14 January 2004 : 22:47:42
|
quote:
Originally posted by grazman
I think this will do what you're looking for: http://www.sqlteam.com/item.asp?ItemID=5857
something about that just doesn't seem to work well for me. first I was not able to reproduce the example, but I'm sure I was in a hurry. second, I want something I can use like a search engine which is what I have finished recently and got it to work using the code in the link I posted earlier in this thread. I appreciate the link you gave me, and maybe I have some use for it, but maybe you can break it down in a working snitz forum example for me... otherwise I am just going to use mine.
thanks. |
|
|
|
|
Topic |
|
Topic Locked
