Post reply

Find records from table based on % of match using full text indexing in sql server 2005

  • December 26, 2008 at 5:18 am

    #383641

    Hi guyz,

    Thanks in advance.

    I am in urgent need of one requirement.Please some body help me

    My requirement is as follows:

    We have one job posting table.This contain attribute like skill,location,salary,created date,description,mobile no etc.

    Now suppose skill column is containing few records:

    skill Location

    java,.net Bangalore

    java,sql server Mumbai

    java Bangalore

    java,c#,sql server Bangalore

    What i need to find is that most relevent records should be orderly arranged in result table.

    For exammple if i am writing query

    SELECT * FROM job_posting WHERE CONTAINS(skill, N'"java"') AND CONTAINS(location, N'"Bangalore"')

    it should return data something like this(more relevent(100% matching) then approximate close (90% matching))

    1>java Bangalore

    2>java,.net Bangalore

    3>java,c#,sql server Bangalore

    4>java,sql server Mumbai

    But it is returning based on index as follows e.g

    1>java,.net Bangalore

    2>java Bangalore

    3>java,c#,sql server Bangalore

    4>java,sql server Mumbai

  • December 26, 2008 at 3:05 pm

    #917944

    Read the following in Books On Line (BOL)

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/996c72fc-b1ab-4c96-bd12-946be9c18f84.htm

    Pay particular attention to the option in the CONTAINS function description:

    Using CONTAINS with weighted_term

    The following example searches for all product names containing the words performance, comfortable, or smooth, and different weightings are given to each word.

    For Example.

    USE AdventureWorks;

    GO

    SELECT Description

    FROM Production.ProductDescription

    WHERE CONTAINS(Description, 'ISABOUT (performance weight (.8),

    comfortable weight (.4), smooth weight (.2) )' );

    GO

    By assigning a low weight to Java and higher weights for .net, sql, etc., etc.,

    Of course you would then want to sort your result in Ascending weighted order, to give you want you specified as the first item, that is

    java Bangalore

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

Viewing 2 posts - 1 through 2 (of 2 total)

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