Skip to main content
A
AJBJ
Inspiring
May 27, 2009
Answered

Search multiple words in SQL

  • 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

    (

    <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.

    1 reply

    D
    Dan_BracukCorrect answer
    Inspiring
    May 27, 2009

    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.

    A
    AJBJAuthor
    Inspiring
    May 27, 2009

    Thanks, that did it.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices