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.......

• Ram Ram

  Ten Centuries

  Points: 1079

  More actions

  October 17, 2006 at 12:49 pm

  #666329

  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

  October 17, 2006 at 1:01 pm

  #666335

  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

  October 18, 2006 at 1:18 pm

  #666624

  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