Problems with SQL Server Full-Text Search on Name Fields

Data and Databases
1

I’m developing an application that uses a SQL Server database, and I’m facing issues with getting the full-text search to work for people’s names. The search is supposed to work across three varchar columns for first names, last names, and middle names.

Here’s an example dataset:

  • 1: Adam - M - Johnson
  • 2: Emma - NULL - Brown
  • 3: Chris - NULL - Brown

When I search for ‘Johnson’, it retrieves the first row correctly. However, if I search for ‘Adam Johnson’, ‘Jo’, or ‘A’, I get no results. The same happens when searching for ‘Brown’ versus ‘Emma Brown’.

I have switched between different search functions like CONTAINSTABLE, FREETEXTTABLE, and others, but I’ve noticed no significant changes in outcomes. The query I’m using currently looks like this:

@Name nvarchar(100)
DECLARE @QueryString varchar(100)
SET @QueryString = '"'+@Name+'"'

SELECT P.Lastname, P.Firstname, P.MiddleName
FROM People as P
INNER JOIN CONTAINSTABLE(People, (Lastname, Firstname, Middlename), @QueryString) AS SearchTable
ON P.Id = SearchTable.[KEY]
WHERE SearchTable.RANK > 2
ORDER BY SearchTable.RANK DESC

What might be causing these issues with partial name searches?

2

are you using exact phrase matching with those double quotes? thats probably why partial searches like ‘jo’ or ‘a’ aren’t working. try dropping the quotes and using wildcard operators like * instead. what does your full-text index setup look like?

3

u might wanna try removing the quotes from @Name. They limit searches to exact matches, which is why partial stuff is failing. Using wildcards like “adam*” could help too. And double-check that your full-text catalog is updated, otherwise it won’t work right.

4

Your problem is the double quotes around @Name - they’re forcing exact phrase matches. That’s why single words work but partial matches don’t. Drop the quotes and use asterisks for prefix matching instead. Also, that RANK threshold of 2 might be too strict and filtering out good results. I had the same issue with a customer database and dropping it to 0 made a huge difference. You might also want to try FREETEXT instead of CONTAINSTABLE for name searches - it handles partial matches and variations way better without all the wildcard mess.