SQL Server 2022 : Full-Text Search2023/11/30

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

(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    
---------- -------------------------- ------------------ --------------------------------------------------
00002      Debian                     Linux              Debian GNU Linux.
00003      Ubuntu                     Linux              Ubuntu is based on the Debian architecture.

(2 rows affected)