Full Search Text multiple columns multiple search

Question

Helo,
I have a user table with name and lastname columns. I created a full search text index on this columns. Search using this query:

*SELECT top 5 * FROM USERS
WHERE
CONTAINS((name, lastname),'BILL AND GATES')*

this query returns users with:

name | lastname
BILL GATES | KENNDY
ROGERS | BILL GATES

but do not return data if query BILL and Gates are on separated columns.

name | lastname
BILL | GATES

what full search text query should i use to return 3 users.

Answer 1

Hi @Anonymous ,

Thanks for your update.

In your situation, you could use a regular = predicate instead of contains as below:

insert into users(name,lastname)  values  
('BILL TURNER','WHITE GATES'),  
('BILL WEST','EAST GATES')  
  
SELECT top 5  name,lastname  FROM USERS  
WHERE  
CONTAINS((name, lastname),'BILL AND GATES')  
	   OR (Name='BILL' AND lastname='GATES')  

OR

 SELECT top 5  name,lastname     
 FROM USERS   
 WHERE (CONTAINS(Name, 'BILL AND GATES')  OR   
       CONTAINS(lastname, 'BILL AND GATES'))  
	   OR (Name='BILL' AND lastname='GATES')  

Output:

name	lastname  
BILL GATES	KENNDY  
ROGERS	BILL GATES  
BILL	GATES  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

What about adding a computed column which is the concatenation of name and lastname, and then full-text index that column?

But you could also do:

WHERE  CONTAINS((name, lastname),'BILL AND GATES') OR
       CONTAINS(name,'BILL') AND CONTAINS(lastnamename,'GATES')) OR
       CONTAINS(name,'GATES') AND CONTAINS(lastnamename,'BILL'))

Answer 3

Hi @Anonymous ,

Welcome to Microsoft Q&A!

Please refer below:

drop table if exists USERS  
  
create table USERS  
(id int identity(1,1),  
name varchar(20),  
lastname varchar(20))  
  
insert into USERS(name,lastname) values  
('BILL GATES','KENNDY'),  
('ROGERS','BILL GATES'),  
('BILL','GATES')  
  
CREATE FULLTEXT CATALOG usersCat1;  
  
CREATE unique INDEX ui_ukDoc ON dbo.USERS(id);    
  
CREATE FULLTEXT INDEX ON dbo.USERS   
(    
    name Language 1033,  
	lastname Language 1033  
)    
KEY INDEX ui_ukDoc ON usersCat1  
WITH CHANGE_TRACKING AUTO          
  
SELECT top 5  name,lastname     
FROM USERS   
WHERE CONTAINS(Name, 'BILL OR GATES')  OR   
      CONTAINS(lastname, 'BILL OR GATES')  

Output:

name	lastname  
BILL GATES	KENNDY  
ROGERS	BILL GATES  
BILL	GATES  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

If you want that result do:

SELECT top 5 * 
FROM USERS
WHERE CONTAINS(name,'"BILL" OR "GATES"') OR
       CONTAINS(name,'"BILL" OR "GATES"')