• Status: Solved
• Priority: Medium
• Security: Public
• Views: 378

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

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
jackjohnson44
1 Solution

Commented:
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

Commented:
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

Commented:
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

Commented:
:-)
thanks!  Glad you like it!  I hope it works for jackjohnson44.

Regards,
peh803
0

Commented:
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

Commented:

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

Commented:
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.

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

Thanks,
peh803
0

Author Commented:
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
0

Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.