How to do a FREETEXTTABLE across multiple multiple columns and multiple terms with ranking?

I've got a customer table like this:

ID*, Title, FirstName, MiddleNames, LastName, CompanyName

All string fields are nullable.

I need to be able to offer the user a fuzzy search. So, for example, they could enter the following searches and it would bring back ranked results:

"BOB" "BOB JONES" "BOB JON*" "MR JONES" "BOB DAVE JONES" "B D JONES" "BOB JONES ACME CORP" "ACME CORP" "ACME BOB" etc.

My problem is that there doesn't seem to be a way to do wildcard/LIKE% matches. So if there is a surname "JONESY", searching "JONES" doesn't match it.

In an ideal world, I'd like to CONCATENATE all the string columns in to开发者_C百科 a single column and then do my fuzzy search on that, because the ranking would be better.

Can anybody tell me how to either do wildcard searches OR search on CONCATENATED fields?

Thanks,

Simon.

You can define a SQL Server full-text index on multiple columns in a table.

Full-Text queries against a table like this can either specify the column for querying or query against all columns at once.

Full-Text search does not support true wildcard matching but it does support prefix matching. This means that you can search for "JONES*" and it will match "JONESON" or "JONESY".

Using FREETEXTTABLE will provide Rank for your results.

A prefix match for "JONES" would look like this:

SELECT
    t.QueryContent
    , ft.[Key]
    , ft.[Rank]
FROM
    Table t
    LEFT OUTER JOIN CONTAINSTABLE ( Table , * , '"JONES*"' ) ft ON ( t.TableID = ft.[Key] )
ORDER BY
    ft.Rank DESC
    , t.QueryContent

i was also doing that kind of search. here is the link which helped me

http://www.codeproject.com/KB/database/SQLServer2K8FullTextSearh.aspx