Search like contains 1

[madanthrax]

Hi,

I have a website that displays Technical Publications. Its ASP with MS SQL. Visitors can browse for publications by topic. I now need to create a search by "title text". I have made an input form and a page to display results using the code below to create the SQL query:

	stext = (request.form("searchtext")
	where = "WHERE Title LIKE '%"& stext &"%' COLLATE SQL_Latin1_General_CP1_CI_AS AND (SeriesID ='32' OR SeriesID ='33')"

SQL = "SELECT PubId, Title, Issued, Released, ElectronicFile, FileSize, SeriesInfo, SeriesText, DocType, FrenchURL, SpanishURL, RussianURL, ArabicURL, ChineseURL, DocType  
FROM dbo.vw_SS_3  "& where &" order by Released desc"

This works but with a major drawback.

A typical title is "Design of Fuel Handling and Storage Systems in Nuclear Power Plants Safety Guide"

If the user types in the search text box "Fuel Handling" a list of publications containing that phrase appear. If the users types in "Fuel Storage" then nothing is found.

I have tried using CONTAINS in the SQL but the source of 90% my data comes from a view from another database so setting up full-indexing to support the CONTAINS seems improbable.

Does anyone have any suggestions how I could work around this problem please. The database has less than 500 publications in it so performance should be a factor.

[sub]"Nothing is impossible until proven otherwise"[/sub]​

 

[madanthrax]

Correction to above: performance should NOT be a factor.

[sub]"Nothing is impossible until proven otherwise"[/sub]​

 

[gmmastros]

If the user enters multiple words, what sort of results do you want?

For example, they enter "Fuel Handling", do you want to return results that contain "fuel" but not "Handling", or do you only want to return results that contain all of the words?

Also.... if you are willing to write a stored procedure (instead of in-line SQL), you can do more interesting things.... like.... you can rank your results so that data with ALL the search terms appear at the top of the list and data that only matches one word appears at the bottom. Understand that the more functionality you add, the code will be more complex, but your users will appreciate the extra effort too.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[madanthrax]

Thanks for the reply gmmastros.

I want to return results that contain all of the words*. Nothing fancy for a first attempt. The search text box is restricted to 50 characters so they could input several words.

*Question: if they input 4 words and one word is not found anywhere will this upset things?

regards,

Anthony.

[sub]"Nothing is impossible until proven otherwise"[/sub]​

 

[vicvirk]

You can split the search string and do a search on each item, using an SP like George mentioned is the best option because you can rank the search results easier but you can do something like this:

<%
<%
	qry = "Fuel Handling"
	sql = "select * from myTable where myField LIKE '%" & qry & "%'"
	ra = split(qry," ")
	if ubound(ra) > 0 then
		for i = 0 to ubound(ra)
			sql = sql & " or myField LIKE '%" & ra(i) & "%'"
		next
	end if
	erase ra
	response.write sql
%>
%>

^
which will generate an sql statement as follows:

select * from myTable where myField LIKE '%Fuel Handling%' or myField LIKE '%Fuel%' or myField LIKE '%Handling%'

That will return all rows that contain the full phrase "Fuel Handling" or any row that contains either word.

--------
GOOGLE is a great resource to find answers to questions like "how do i..."

If you don't know exaclty what you want to do or what to search on, try Google Suggest:

http://www.google.com/webhp?complete=1&hl=en

--------
I have recently been semi-converted to ensuring all my code (well most of it) works in both javascript and non-javasc

 

[gmmastros]

If you plan on using an OR condition in the where clause, make sure you also use parenthesis. This is VERY important. Without the parenthesis, you will likely get incorrect results.

Select Columns
From   Tables
WHERE  [!]([/!]Title LIKE '%Fuel%' COLLATE SQL_Latin1_General_CP1_CI_AS 
       Or Title Like '%Handling%' COLLATE SQL_Latin1_General_CP1_CI_AS[!])[/!]
       AND (SeriesID ='32' OR SeriesID ='33')

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[madanthrax]

Thanks all,

vicvirk, now that I see your code I realise likely queries will find a lot of matches, nuclear related words will repeatedly appear in most of the titles. If I change the OR to AND it will be more accurate but runs a high risk of returning nothing.

So you guys are right the SP is the way to go, at least it puts the most likely at the top of the list. I wrote one SP about 5 years ago, so any links to help with a search SP would be gratefully appreciated.

regards.

[sub]"Nothing is impossible until proven otherwise"[/sub]​