sql Search

[grabit]

Hi peoples
I have the following query to return search results. How can i stop this from returning results containg words or phrases like
"Hi Team, please check latest news. Cheers Rach"
when i search for "test" and the search string is contained in the word "latest"

I just want to search for and return the whole words

Query is
<cfquery name="searchResults" datasource="#dsn#">
SELECT threadID, posttype, topic, topicID, postdate, username, threads.catID, posttext, categories.catID, category
FROM threads INNER JOIN categories
ON threads.catID=categories.catID
WHERE topic LIKE '%#form.subject#%' OR username LIKE '%#form.subject#%' OR posttext LIKE '%#form.subject#%'
</cfquery>

[MMcCarthy]

You need to impose spaces between the search word and the wildcards as follows:

<cfquery name="searchResults" datasource="#dsn#">
SELECT threadID, posttype, topic, topicID, postdate, username, threads.catID, posttext, categories.catID, category
FROM threads INNER JOIN categories
ON threads.catID=categories.catID 
WHERE topic LIKE '% ' & '#form.subject#' & ' %' 
OR username LIKE  '% ' & '#form.subject#' & ' %' 
OR posttext LIKE '% ' & '#form.subject#' & ' %' 
</cfquery>

[MMcCarthy]

If you need to include words at the beginning and end of sentences which don't have spaces before and after them then you will need to allow for that in the code as follows:

WHERE ((topic LIKE '% #form.subject# %' 
OR topic LIKE '#form.subject# %'
OR topic LIKE '% #form.subject#')
OR (username LIKE  '% #form.subject# %' 
OR username LIKE  '#form.subject# %' 
OR username LIKE  '% #form.subject#')
OR (posttext LIKE '% #form.subject# %' 
OR posttext LIKE '#form.subject# %'
OR posttext LIKE '% #form.subject#'))
 

Watch the brackets.

Mary

[grabit]

If you need to include words at the beginning and end of sentences which don't have spaces before and after them then you will need to allow for that in the code as follows:

WHERE ((topic LIKE '% #form.subject# %' 
OR topic LIKE '#form.subject# %'
OR topic LIKE '% #form.subject#')
OR (username LIKE  '% #form.subject# %' 
OR username LIKE  '#form.subject# %' 
OR username LIKE  '% #form.subject#')
OR (posttext LIKE '% #form.subject# %' 
OR posttext LIKE '#form.subject# %'
OR posttext LIKE '% #form.subject#'))
 

Watch the brackets.

Mary

I have this which seems to work
<cfquery name="searchResults" datasource="#dsn#">
SELECT threadID, posttype, topic, topicID, postdate, username, threads.catID, posttext, categories.catID, category
FROM threads INNER JOIN categories
ON threads.catID=categories.catID
WHERE topic LIKE '#form.subject# %' OR topic LIKE '% #form.subject#' OR topic LIKE '% #form.subject# %' OR topic LIKE '#form.subject#'
OR username LIKE '#form.subject#'
OR posttext LIKE '#form.subject# %' OR posttext LIKE '% #form.subject#' OR posttext LIKE '% #form.subject# %' OR posttext LIKE '#form.subject#'
</cfquery>

[MMcCarthy]

Sounds good, the bracket just make it tidier but shouldn't affect preformance.

Mary