Search multiple words in SQL

Forum|Forum|17 years ago
May 27, 2009
1 reply
961 views

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#%">
        )

This topic has been closed for replies.

Correct answer Dan_Bracuk

something like this:

select somefields

from sometables

where 1 = 1

and

(

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.

D

Dan_BracukCorrect answer

Inspiring

something like this:

select somefields

from sometables

where 1 = 1

and

(

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.

A

AJBJAuthor

Inspiring

Thanks, that did it.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded