Doing partial match on name fields in SQL Server

Question

I need to enable partial matching on name search. Currently it works with Like '%@name%' but it's not good enough.

We need to enable typing in both first name and last name and both need to be partial, so I'm assuming full text is the way to go.

The problem is that I can't get it do a partial match on a name. For example searching for my name (Fedor Hajdu) will work if I type in either parts in full but not partial (It should match a search for 'fe ha' for example.

How can I achieve this? Can fulltext index be set to do something like syllable matching?

Unhouse

Answer 1

humm three options that mya help you:

1. the INFLECTIONAL form (Link)
2. CONTAINS with NEAR (Link)
3. CONTAINS with Weighted Values (Link)

If that doesn't help, get the string 'fe ha' and add a '%' on every blank space and do a Like:

Like '%fe%ha%'

Sena

Neither of those helped (at least in the way I used them) – Unhouse

Answer 2

Using CONTAINS() or CONTAINSTABLE() all you need to do is add * at the end of your matching string:

CONTAINS (Description, '"top*"' );

If you have your string as a parameter you may concatenate like this:

SET @SearchTerm = '"' + @NameParameter + '*"'

CONTAINS (Description, SearchTerm );

https://technet.microsoft.com/en-us/library/ms142492(v=sql.105).aspx

Laraelaraine