Solved

SQL count how many times a word exists in a text field or memo

Posted on 2004-10-06
9
369 Views
Last Modified: 2008-02-01
Hello,
I am trying to make a search box on a database driven website with dynamic content.

I would like to be able to search using keywords on a text field.

I would like to rank the search by the number of times that it exists in the field.

So if one row contained the word 2 times, it would be ordered higher than a row that had the keyword only once.

How would I do this?

thanks
0
Comment
Question by:jackjohnson44
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 19

Accepted Solution

by:
peh803 earned 250 total points
ID: 12241424
To get the number of occurrences of a given word in a larger string, you could try something like this (where myTable is your table, Str is the field you're searching in the table, and 'Apple' is the word you're searching for):

SELECT (LEN(Str)-LEN(REPLACE(Str, 'Apple', '')))/(LEN(str)) FROM myTable

regards,
peh803
0
 
LVL 19

Expert Comment

by:peh803
ID: 12241449
Here's the logic behind how my last post works:

str = "Take me out to the ball game, take me out to the crowd"
len(str) = 54
len(replace(str, "take", "")) = 46
54-46=8
8/len("take") = 8/4 = 2

Regards,
peh803
0
 
LVL 10

Expert Comment

by:ALaRiva
ID: 12241550
peh803,
    that's beautiful logic, I wish we had more people who could think outside the box like that.

    A truly overlooked method of SQL Statements is also the In() Method.  It can be so useful, just that most don't know about it.

Sorry for the hi-jack, it's just that you don't find many who can think outside the box.

-Anthony

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 19

Expert Comment

by:peh803
ID: 12242273
:-)
thanks!  Glad you like it!  I hope it works for jackjohnson44.

Regards,
peh803
0
 
LVL 12

Expert Comment

by:minichicken
ID: 12242493
peh803, nice code! and unique. I enjoy see codes like this, easy and with explanation of logic, helps people understand better of what they are actually using......

Good job!
0
 
LVL 19

Expert Comment

by:peh803
ID: 12242587
Thanks!  Glad you like it!

I didn't know of a "built-in" function to do it, so I just kinda came up with it spur of the moment.

Regards,
peh803
0
 
LVL 19

Expert Comment

by:peh803
ID: 12250489
I was thinking about this as I was laying in bed last night, and I thought of a potential flaw:

SELECT (LEN(Str)-LEN(REPLACE(Str, 'Apple', '')))/(LEN(str)) FROM myTable

str = "Take me to a basketball game, football game, or any other ballgame."

Searching this string for "ball" based on the above logic will produce a count of 3, while the word "ball" doesn't actually occur anywhere in the string (but rather it appears 3 times as a subset of other words).  To alleviate this, you might consider searching using leading and trailing spaces, like this:

str = "The pitcher threw the ball, and the baseball player hit the baseball."
SELECT (LEN(Str)-LEN(REPLACE(Str, ' ball', '')))/(LEN(' ball')) FROM myTable

Notice that I have used only a leading space, because I don't want to exclude the word "ball,".  Using a trailing space would cause the word " ball," to not be replaced because of the comma.  

At any rate, these are just a few other things to consider.

Any questions / comments about this solution?  

Please let me know how it's working for you.

Thanks,
peh803
0
 

Author Comment

by:jackjohnson44
ID: 12250797
Thanks for the advice peh803.  I haven't tried it, but I like your way of thinking.
It didn't occur to me to replace all words except the search term.
It is simple and very effective.

I think the "potential flaw" could work to my advantage, since it would allow
singular key words to match up with plurals.  At any rate, thanks for
both answers.
0
 
LVL 19

Expert Comment

by:peh803
ID: 12251078
you're welcome -- just wanted to point out the potential flaw incase it might pose a problem for you.  But if not, all the better!

Happy to help; perhaps others will find this useful as well.

Thanks,
peh803
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

749 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