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

• 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: 323741

  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