Search within a description

[rmz8]

How can I make a search function out of SQL that searches within a description. For example, let's say someone described something with the following text:

This is a Chemistry assignment on the composition of oxygen and hydrates.

How can I make it so that an enduser types in queries like:

oxygen composition
composition
oxygen
hydrates
oxygen hydrates

and have that article come up as pertient?

I'm using ColdFusion w/o MS SQL, so SQL functions might be a little limited.

Thanks!

Ryan ;-]

 

[THoey]

Would something like the following work:

SELECT * from tblArticles
WHERE Description LIKE '%oxygen composition%';

HTH

Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[karluk]

Your request is complicated because you will need to write a parser to split the entered text into individual words. Then the where clause will look something like

where description like '%word1%'
and description like '%word2%'
and ...
and description like '%wordn%'

There also will be issues about whether upper and lower case letters should be treated equally.

Terry's suggestion has a lot of merit as an easy way to get a lot of this functionality without too much work. Instead of just putting % at the beginning and end of the string, however, I would replace all spaces with %. Then the main problem will be that the users of your search function will have to enter the words in the same order they appear in the description.

 

[rmz8]

Yeah, that will not be sufficient because it has to search the entire description and not simply match if one word or phrase is in it. I don't know if you guys know ColdFusion, but this is what I have already created. It functions pretty well, but I want something a bit more solid. It works on a loop and step function. Here it is (where #searchwords# are the words inputted on the form and the bolded part is the SQL that makes it work):

<cfset sql=&quot;1 = 0&quot;>
<cfloop index=&quot;x&quot; list=#searchWords# delimiters=&quot; &quot;>
<cfset sql=&quot;#sql# or resource_description like '%#replace(x,&quot;'&quot;,&quot;''&quot;,&quot;all&quot#%'&quot;>
</cfloop>

<cfquery datasource=&quot;#dsn#&quot; name=&quot;desc&quot;>
select * from resources,users where 1=1 and #preservesinglequotes(sql)#
</cfquery>

Ryan ;-]

 

[karluk]

Your code isn't ANSI sql, so it might be better to post this to the Cold Fusion forum.

Concerning your code, may I ask you to be more specific about what problems you are experiencing? As far as I can decipher the Cold Fusion syntax it appears very similar to my suggestion, except that you are using OR instead of AND in searching for multiple words. If your code is working I would be very reluctant to replace it with something &quot;a bit more solid&quot;.

 

[rmz8]

I agree, and thanks!

Ryan ;-]