FullText search by FullName when FullName is not a column, but has firstName and LastName

Question

I have a below query, i do not have a fullName field , but queryText input can have fullname. my search by firstName or lastName works, but user provides both first and last name it does not work.

@queryText of 
John Works
Doe  Works
John Doe  Does not work

CREATE FULLTEXT INDEX ON [HK].[User]
 (   
  UserName,
  FirstName,
  LastName,
  NickName,
  EmailAddress,
  WorkPhone  
 )   
  KEY INDEX [PK_HK.User]
      ON [HKUserCatalog]
      WITH CHANGE_TRACKING AUTO ,
      STOPLIST = SYSTEM
      ;   
GO 

my sql

    SELECT TOP(@queryLimit) * FROM [User]
            WHERE
                CONTAINS((UserName, FirstName, LastName, NickName, EmailAddress, WorkPhone), @queryText)
                OR TRY_CONVERT(INT, @queryText) = UserId
                Order By FirstName, LastName
                ;

How do i make it work without adding a fullName column to the table? i tried using http://www.22bugs.co/post/searching-peoples-names-with-sql-server-full-text-search/ but does not work either

andrews · Answer 1 · 2018-01-12T23:06:34.230

I see you don't want to add the FullName column to your table but in case you change your mind and If you are allowed to modify your [HK].User table then here is how you can add a computed column called FullName and include it in your full text index:

alter table [HK].[User] add [FullName] as (isnull(FirstName,'') + ' ' + isnull(LastName,''))

alter fulltext index on [HK].[User] disable
alter fulltext index on [HK].[User] add (FullName)
alter fulltext index on [HK].[User] enable

Then include this column in your CONTAINS predicate:

SELECT TOP(@queryLimit) * FROM [User]
       WHERE
           CONTAINS((UserName, FullName, NickName, EmailAddress, WorkPhone), @queryText)
           OR TRY_CONVERT(INT, @queryText) = UserId
       Order By FirstName, LastName
       ;

This way when FirstName or LastName columns values change the full text index for FullName will be auto updated and all your provided sample queries should return desired result.

Note, if you want you can even include Nickname column into FullName definition and it will make your CONTAINS condition even smaller and easier to read.

I personally think a computed FTS-indexed column is the best for this kind of task unless you have restrictions which may prevent you from adding it.

Hope this helps.

score 0 · Answer 2 · answered Jan 11 '18 at 18:29

0

The example which you provided has only one Name column, it is not same with your case. When you want to search "John Doe", the Full Text Search fetchs the condition for per column. So, you should create multiple Contains by using AND;

SELECT * FROM [User] WHERE
CONTAINS(FirstName, '"John"') and CONTAINS(LastName, '"Doe"');

answered Jan 11 '18 at 18:29

lucky

12,734
4
24
46

but my query could be just John or Doe or John Doe – Justin Homes Jan 11 '18 at 19:52
You can't perform it by using this design. You should create just "Name" column then. – lucky Jan 11 '18 at 20:05

FullText search by FullName when FullName is not a column, but has firstName and LastName

2 Answers2