SQL Server 2022 : Full-Text Search2023/01/27

# テスト用サンプルテーブル
1> select * from dbo.Sample_Table; 
2> go
Number     First_Name      Last_Name        Description    
---------- --------------- ---------------- ----------------------------------------------------
00001      Ubuntu          Linux            Ubuntu is based on the Debian architecture.
00002      Debian          Linux            Debian GNU Linux.
00003      RedHat          Linux            RedHat Enterprise Linux.
00004      CentOS          Linux            This is the Community Enterprise Operating System.
00005      Windows         Microsoft        Microsoft Windows

(5 rows affected)

# [Sample_TBCat] という名称のフルテキストカタログ作成
1> create fulltext catalog Sample_TBCat;
2> go

# [Number] 列に対して [UIDoc] という名称のユニークインデックス作成
1> create unique index UIDoc on Sample_Table(Number);
2> go

# [Sample_TBCat] カタログにフルテキストインデックス作成
1> create fulltext index on Sample_Table(First_Name,Description)
2> key index UIDoc on Sample_TBCat;
3> go


# [First_Name,Description] 列に対して [Enterprise] キーワードが含まれる行を検索
1> select * from dbo.Sample_Table where contains((First_Name,Description),'Enterprise');
2> go
Number     First_Name        Last_Name      Description    
---------- ----------------- -------------- -----------------------------------------------------
00004      CentOS            Linux          This is the Community Enterprise Operating System.
00003      RedHat            Linux          RedHat Enterprise Linux.

(2 rows affected)

# [Description] 列に対して [GNU] または [architecture] キーワードが含まれる行を検索
1> select * from dbo.Sample_Table where freetext((Description),'GNU or architecture');
2> go
Number     First_Name     Last_Name       Description    
---------- -------------- --------------- ----------------------------------------------
00001      Ubuntu         Linux           Ubuntu is based on the Debian architecture.
00002      Debian         Linux           Debian GNU Linux.

(2 rows affected)