CODEWITHSUNDEEP
sql-serversql-server-2012full-text-search
Justin Homes
Justin Homes

Reputation: 3799

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

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?

Upvotes: 0

Views: 1255

Answers (2)

andrews
andrews

Reputation: 2173

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.

Upvotes: 1

Emre Kabaoglu
Emre Kabaoglu

Reputation: 13146

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"');

Upvotes: 0

Related Questions