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

[maxspeed]

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

[dempster]

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.

[maxspeed]

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

[Dan_Bracuk]

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

[maxspeed · Accepted solution]

many thanks, perfect