如何使用Microsoft SQL CONTAINS搜索列在任何位置都包含单词的行，类似于LIKE'％word％'? [英] How to use Microsoft SQL CONTAINS to search for rows where the column contains a word at any position, similar to LIKE '%word%'?

问题描述

我的客户"表在名称"列上具有全文索引.(我通过使用

My Customer table has a full-text index on column Name. (I checked that the column is full-text index by using this answer and by right clicking on the column -> properties -> Full Text = true)

名字通常是名字和姓氏，例如

The names are usually first name and surname, e.g.

Name
---------------
Joe
Joe Doe
Joe Bar
Foo Joe
Alice Bob
Charles David
Elizabeth Fred

当我搜索名称中包含"Doe"一词的所有客户时，使用包含谓词

When I search for all customers where the name contains the word "Doe" using the CONTAINS predicate

SELECT * FROM mst_Customers WHERE CONTAINS(Name,'Doe')

一无所获

当我搜索名称中包含"Joe"的所有客户时

When I search for all customers where the name contains "Joe"

SELECT * FROM mst_Customers WHERE CONTAINS(Name,'Joe')

将转换前3条记录(而不是名称包含单词Joe的所有4个客户)

the first 3 records are turned (instead of all 4 customers where the name contains the word Joe)

Name
---------------
Joe
Joe Doe
Joe Bar

如何使用 CONTAINS 返回名称中某处包含"Doe"的所有客户?

How do I use CONTAINS to return all customers that somewhere in the name contain "Doe"?

使用LIKE的等效方法

The equivalent using LIKE

SELECT * FROM mst_Customers WHERE Name LIKE '%Doe%'

给我想要的结果.

推荐答案

这很完美...演示:

CREATE TABLE T_NOM
(NOM_ID INT IDENTITY CONSTRAINT PK_NOM PRIMARY KEY, 
 NOM_NOM VARCHAR(32))

INSERT INTO T_NOM VALUES 
('Joe'), 
('Joe Doe'), 
('Joe Bar'), 
('Foo Joe'), 
('Alice Bob'), 
('Charles David'), 
('Elizabeth Fred');
GO

CREATE FULLTEXT CATALOG FT;
GO

CREATE FULLTEXT INDEX ON T_NOM (NOM_NOM) KEY INDEX PK_NOM ON FT;
GO

SELECT * FROM T_NOM WHERE CONTAINS(NOM_NOM,'Doe');

这篇关于如何使用Microsoft SQL CONTAINS搜索列在任何位置都包含单词的行，类似于LIKE'％word％'?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！