By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,982 Members | 1,939 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,982 IT Pros & Developers. It's quick & easy.

sql Search

P: 22
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>
Mar 12 '07 #1
Share this Question
Share on Google+
4 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
You need to impose spaces between the search word and the wildcards as follows:

Expand|Select|Wrap|Line Numbers
  1. <cfquery name="searchResults" datasource="#dsn#">
  2. SELECT threadID, posttype, topic, topicID, postdate, username, threads.catID, posttext, categories.catID, category
  3. FROM threads INNER JOIN categories
  4. ON threads.catID=categories.catID 
  5. WHERE topic LIKE '% ' & '#form.subject#' & ' %' 
  6. OR username LIKE  '% ' & '#form.subject#' & ' %' 
  7. OR posttext LIKE '% ' & '#form.subject#' & ' %' 
  8. </cfquery>
Mar 12 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
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:
Expand|Select|Wrap|Line Numbers
  1. WHERE ((topic LIKE '% #form.subject# %' 
  2. OR topic LIKE '#form.subject# %'
  3. OR topic LIKE '% #form.subject#')
  4. OR (username LIKE  '% #form.subject# %' 
  5. OR username LIKE  '#form.subject# %' 
  6. OR username LIKE  '% #form.subject#')
  7. OR (posttext LIKE '% #form.subject# %' 
  8. OR posttext LIKE '#form.subject# %'
  9. OR posttext LIKE '% #form.subject#'))
  10.  
Watch the brackets.

Mary
Mar 12 '07 #3

P: 22
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:
Expand|Select|Wrap|Line Numbers
  1. WHERE ((topic LIKE '% #form.subject# %' 
  2. OR topic LIKE '#form.subject# %'
  3. OR topic LIKE '% #form.subject#')
  4. OR (username LIKE  '% #form.subject# %' 
  5. OR username LIKE  '#form.subject# %' 
  6. OR username LIKE  '% #form.subject#')
  7. OR (posttext LIKE '% #form.subject# %' 
  8. OR posttext LIKE '#form.subject# %'
  9. OR posttext LIKE '% #form.subject#'))
  10.  
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>
Mar 12 '07 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
Sounds good, the bracket just make it tidier but shouldn't affect preformance.

Mary
Mar 12 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.