How to query SQL Server 2008 database for first and last name and order by relevance?

sql, sql-server, sql-server-2008, t-sql

Solution

As an addendum to OMG Ponies answer...

To get the best results from a full text search you might want to create an indexed view that concatenates the first and last name fields.

This will allow you to weight individual parts of the full name more precisely.

Example code as follows:

    CREATE VIEW [dbo].[vFullname] WITH SCHEMABINDING AS
    SELECT personID, FirstName + ' ' + LastName AS name 
    FROM dbo.person 

    WITH ranks AS(
    SELECT FT_TBL.personid 
        ,FT_TBL.name
        ,KEY_TBL.RANK
    FROM dbo.vfullname AS FT_TBL 
        INNER JOIN CONTAINSTABLE(vfullname, (name),
    'ISABOUT ("Smith" WEIGHT (0.4), "Smi*" WEIGHT (0.2),
"John" WEIGHT (0.3), "Joh*" WEIGHT (0.1))') AS KEY_TBL  
          ON FT_TBL.personid = KEY_TBL.[KEY]
    ) 
    SELECT
    r.personID,
    p.firstname,
    p.lastname,
    r.rank
    FROM ranks r INNER JOIN
    person p ON r.personID = p.personID
    ORDER BY rank DESC;

The CTE just allows you to return the individual firstname and lastname fields. If you don't need these as an output then ignore it.

Problem

Basically I have a table like this: ``` CREATE TABLE Person( PersonID int IDENTITY(1,1) NOT NULL, FirstName nvarchar(512) NOT NULL, LastName nvarchar(512) NULL ) ``` And I need to find the top n results based on a user-query like this: ``` "Joh Smi" ``` The following query returns the results I need (I think). Just not in the relevant order. ``` SELECT PersonID, FirstName, LastName FROM Person WHERE FirstName LIKE 'Joh%' OR LastName LIKE 'Joh%' OR FirstName LIKE 'Smi%' OR LastName LIKE 'Smi%' ``` If the following names were in the database and our user-query was "Joh Smi" the names should appear in the following order (or similar) - John Smith - Johnny Smith - John Jacob - David Smithsonian - Daniel Johnson I'm hoping to get it to work similar to facebook's autocomplete friend-search. So, how do I return the top n most relevant rows in SQL Server 2008?

Original source