Wally Kolcz wrote:
> Can anyone help me write the SQL for a Full-Text Search
for keywords in a
> database? I have a simple search engine on my site that
allows customers to
i guess first off, do you actually have full text indexing
setup for that table
on the "keywords" column? and is it updated? if you do then:
SELECT company, name, phone, service, city, state, username
FROM members
WHERE service = #arguments.service# AND state =
'#arguments.state#'
<cfif len(trim(arguments.keywords)) GT 0>
AND CONTAINS(keywords,'#arguments.keywords#')
</cfif>
ORDER BY company ASC
or if you want "meaning" instead of exact wording:
SELECT company, name, phone, service, city, state, username
FROM members
WHERE service = #arguments.service# AND state =
'#arguments.state#'
<cfif len(trim(arguments.keywords)) GT 0>
AND FREETEXT(keywords,'#arguments.keywords#')
</cfif>
ORDER BY company ASC
you'd use the CONTAINSTABLE or FREETEXTTABLE if you needed to
join the results
back to something as well as get a "rank" for the search
results.
if the keywords var is a list then you want to build your
WHERE clause a bit
differently:
- qualify them w/double quotes
- separate them by ORs
- wrap the whole mess in single quotes ie WHERE
CONTAINS(Description, '"sea*" OR
"bread*"')
your data sounds messed up, i don't think you want to
actually store the commas
as they imply being part of the text & i guess you mean
them to be delimiters to
separate values, which won't have any meaning in full-text
indexing. but if
that's what you actually want, then take paross's advice
& move those to another
table, one keyword phrase per row.
btw this is all in sql server's books-on-line.