Search multiple words in SQL

[AJBJ]

I want to be able to search on multiple words in sql.  If someone inputs into the search field multiple words like 'biofuels corn industry' . I want to return all records that has any of those words in them.  Below is my code:(which only brings back the phrase and not individual words from the database)

and (
            path like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#arguments.searchterms#%">   
            or
            body like <cfqueryparam cfsqltype="cf_sql_longvarchar" value="%#arguments.searchterms#%">   
            or
            summary like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#arguments.searchterms#%">
            or
            author like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#arguments.searchterms#%">
        )

[Dan_Bracuk · Accepted solution]

something like this:

select somefields

from sometables

where 1 = 1

and

(

<cfloop list = "biofuels corn industry" index = "word" delimiters = " ">

or concat(field1, field2, etc) like '%#word##%'

</cfloop>

or 1 = 2

)

Just so you know, this will be slow.   Also, if any of those fields allow nulls, you'll have to deal with that.

[AJBJ]

Thanks, that did it.