Implementing multiple keyword search feature in site.. How to????

Question

Post reply

Implementing multiple keyword search feature in site.. How to????

ajashadi

Ten Centuries

Points: 1017
More actions
October 17, 2006 at 9:21 am

#192720

Hi,
I want to implement a multiple words search feature on my site wherein user will enter something like "regulations leaves employee" in the textbox. I have four tables
Regulations(reg_id,Reg_name),
Reason(Reas_Reg_ID, Reason_text),
Notes(note_Reg_Id, Note_text),
Eligibility(Elig_Reg_ID, Elig_Text)
and need to search text in search textbox in the these columns Reg_name, Reason_Text, Note_text, Elig_Text.
here is the query I am using
Code:
SELECT reg.Reg_Name, reg.State,reg.MaxDuration, reg.Concurrent_Policies, reg.Link, elig.Eligibility_Text, reas.Reason_Text, note.Note_Text FROM (( (Regulations reg inner join Reason reas ON reg.Reg_ID = reas.Reason_Reg_ID and (reg.Reg_Name like '%#txtKeyword#%' or reg.MaxDuration like '%#txtKeyword#%' or reas.Reason_Text like '%#txtKeyword#%') ) inner join Eligibility elig on reg.Reg_ID = elig.Elig_Reg_ID and elig.Eligibility_Text like '%#txtKeyword#%' ) inner JOIN Notes note on reg.Reg_ID = note.Note_Reg_ID and note.Note_Text like '%#txtKeyword#%' )
I am sorry if code is not indented well, plz copy it in ur text editor and have a look at it.
My questions are
1) How ppl implement multiple word search on sites? My initial thinking is you can break the words seperated by spaces into different single/individual words but i am not sure...
2)Its easy to do search on one table... how to do this on multiple tables?
Thanks in advance.. I am wondering how search on google shows results even if you enter a sentence with 5-10 words and also does AUTOCHECK of spelling... I wish I could get the logic for this kind of search feature.......

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ram Ram Ten Centuries Points: 1079 More actions · Answer 1

Hi

Try this

DECLARE @ProdList VARCHAR(100)

SET @ProdList = 'regulations leaves employee'

SELECT *

FROM Regulations AS REG

INNER JOIN Reason AS REAS

ON REG.Reg_ID = REAS.Reason_Reg_Id

AND ((CHARINDEX(CONVERT(NVARCHAR,REG.Reg_Name),@ProdList) > 0)

OR (CHARINDEX(CONVERT(NVARCHAR,REG.MaxDuration),@ProdList) > 0)

OR (CHARINDEX(CONVERT(NVARCHAR,REG.Reason_Text),@ProdList) > 0))

INNER JOIN Eligibility AS ELIG

ON REG.Reg_ID = ELIG.Elig_Reg_ID

AND (CHARINDEX(CONVERT(NVARCHAR,ELIG.Eligibility_Text),@ProdList) > 0)

INNER JOIN Notes AS NOTE

ON REG.Reg_ID = NOTE.Note_Reg_ID

AND (CHARINDEX(CONVERT(NVARCHAR,ELIG.Note_Text),@ProdList) > 0)

Ram

Lowell SSC Guru Points: 323518 More actions · Answer 2

I think you'll want to avoid any functions like CONVERT and CHARINDEX in your joins/where clauses, as that requires a full table scan in order to get the data so it can be compared;

whenever possible, you'd want to use the same data type; ie varchar with varchar as well.

At a minimum, if you are going to use a column in a search like that, make the column part of the CLUSTERED index of the table...if it's clustered, the data is in the index itself, and a query plan would not have to seek or scan the table to do the comparison, i think.

I think the correct solution isvoves creating a full text catalog, so that you can get a match on "leaves" with leaving/ leave and all the other advantages of a full text catalog;

there's a decent article here on SSC to get you started:

Understanding SQL Server Full-Text Search, Part I

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ajashadi Ten Centuries Points: 1017 More actions · Answer 3

Hi,

Ram your query is almost like mine except that you have used functions.... As for Lowell, I am not sure if I want to do Full-Text Search. I understand about index and how it can speed up and things like that. But I am in the first phase now and I need a RIGHT query to get the results I need. For example when you search on google you get results highlighted by keywords you searched for... so i was wondering if they break the words into individual results or what...

Anyone knows how to do this??

Thanks