Skip to main content
M
maxspeed
Inspiring
August 10, 2006
Answered

using 'like' to search multiple words in sql search phrase

  • August 10, 2006
  • 2 replies
  • 1606 views
HI, At present I have a cfquery as follows

<cfquery name="test" datasource="#datasource#" cachedwithin="#CreateTimeSpan(0,1,0,0)#">
SELECT * FROM #tablename# Where entryname is not null and (entryname like '%#keyword#%')
order by sequencename
</cfquery>

If the entryname fiield is for example "Shepton Mallett", it will return if I search for "Shepton" or "Mallett" or "Shepton Mallett", however if I spell 1 word of the search phrase wrong "i.e Shepton Mallatt", it finds no results. I realise I need to split the search phrase and search on both words, but not quite sure what the right way of doing it is. Also I wanted to strip common words (i.e the,a, etc etc) from the search phrase so it does not return loads of results. I would prefer to keep it in sql (not verity) if possible

many thanks

Oli

    This topic has been closed for replies.
    Correct answer maxspeed
    many thanks, perfect

    2 replies

    D
    Dan_Bracuk
    Inspiring
    August 31, 2006
    If you set your sql to a variable, use the PreserveSingleQuotes function.

    By the way, in your query, this part is unnecessary:
    where mailingname is not null
    M
    maxspeedAuthorCorrect answer
    Inspiring
    August 31, 2006
    many thanks, perfect
    D
    dempster
    Inspiring
    August 10, 2006
    You can treat the keyword that was entered as a list delimited by spaces, and use a SQL OR to check. Create the query string like this:

    <CFSET sqltext = "">
    <CFLOOP INDEX="oneterm" LIST="#Form.keyword#" DELIMITERS=" ">
    <CFIF len(sqltext) GT 0><CFSET sqltext = sqltext & " OR "></CFIF>
    <CFSET sqltext = sqltext & "entryname LIKE '%#oneterm#%'">
    </CFLOOP>

    In your example, this would create the string:

    entryname LIKE '%Shepton%' OR entryname LIKE '%Mallett%'

    If you want to refine this more, you'd need a list of words to exclude which you could do as you create the query string.
    M
    maxspeedAuthor
    Inspiring
    August 31, 2006
    dempster, many thanks for that, it works a treat apart from the cfquery does not like executing the dyanmic code, even if I build the whole sql text in a variable before.

    for example the following code executes fine



    <cfquery name="#search#" datasource="#datasource#" cachedwithin="#CreateTimeSpan(0,1,0,0)#">
    SELECT * FROM #tablename# where mailingname is not null and mailingname like '%shepton%'</cfquery>

    but the following does not, I get an invalid column"shepton" error, but the structure of the 2 queries is exactly the same!!

    <cfset sqltext = "and mailingname like '%shepton%'">

    <cfquery name="#search#" datasource="#datasource#" cachedwithin="#CreateTimeSpan(0,1,0,0)#">
    SELECT * FROM #tablename# where mailingname is not null #sqltext#</cfquery>


    many thanks

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