Search Query Help

[1CoolDude]

I'm on CF 5, Windows 2000. I'm trying to build a database search, and I'd love to index the results, but Verity indexing is broken. Our server was hit with a couple of viruses, and the indexing is completely messed up. I think we could simply reinstall the Verity portion, but my boss says that's not going to happen.

I have a radio button to search by any word, all words or exact phrase. I tried to see what was out there already and follow their advice, but it's not working properly. I'm sure that the queries are not correct.

A search on any word returns results out of the section. A search on all words brings back nothing. A search on exact term works fine. If you put in multiple search terms, only the last one is returned.

Any ideas?

[insuractive]

I think the key here may be your use of parenthesis and the AND/OR keywords:

SQL evaluates the following WHERE clauses much different:

EXAMPLE 1
=================
WHERE col1='A'
AND col2 like '%B%'
OR col3 like '%C%'

vs

EXAMPLE 2
====================
WHERE col1='A'
AND (col2 like '%B%' OR col3 like '%C%')

Example 2 is probably more like what you want. In addition to that, if you are looping over a number of keywords and you want to match any of them (not all of them) you should probably use the OR keyword inside your loop:

EXAMPLE 4 (you coldfusion code should output something like this:)
===================
WHERE col1='A'
AND (
(col2 like '%iLoopValue1%' OR col3 like '%iLoopValue1%')
OR (col2 like '%iLoopValue2%' OR col3 like '%iLoopValue2%')
)

Hope that helps!

[1CoolDude · Accepted solution]

Thanks, insuractive. You got me thinking about how the queries were written and I fixed it. I still don't know why it was having trouble with a space as a delimiter, but I just replaced the spaces with a pipe like this:
<CFIF search_type IS NOT "3"><cfset keyword = #Replace(keyword,chr(32),"|","all")# ></CFIF>
and no problem.