獲取SELECT列表中的匹配條件

假設我的全文搜尋查詢如下：

USE AdventureWorks2012;  
GO  
SELECT Name  
FROM Production.Product  
WHERE CONTAINS(Name, '"chain*" OR "full*"');  
GO  

我還需要一列來顯示CONTAINS子句中匹配的條件。

SELECT Name ,Matched_condition 
FROM Production.Product  
WHERE CONTAINS(Name, '"chain*" OR "full*"');  

+---------------------+-------------------+
| name                | matched_condition |
+---------------------+-------------------+
| chain smokers       | chain             |
+---------------------+-------------------+
| full throttle       | full              |
+---------------------+-------------------+
| chain full of smoke | chain,full        |
+---------------------+-------------------+

我應該使用什麼來獲取新列 ( matched_condition)？

我有一個包含 200 個單詞的條件列表。

可能的實現。全文搜尋替換為常見的 LIKE 子字元串搜尋。

源表：

CREATE TABLE test (val VARCHAR(16));

INSERT INTO test VALUES
('1 chaining'),
('2 fullfill'),
('3 full chain'),
('4 no pattern');

模式表（可以是臨時變數或表類型變數）：

CREATE TABLE patterns (pattern VARCHAR(8));

INSERT INTO patterns VALUES
('%chain%'),
('%full%');

查詢：

SELECT test.val, STUFF( ( SELECT ',' + pattern
                         FROM ( SELECT test.val, patterns.pattern
                                FROM patterns
                                WHERE test.val LIKE patterns.pattern
                              ) tmp
                         FOR XML PATH('') 
                       ), 1, 1,'' 
                     ) matched_patterns
FROM test;

查詢結果：

   val      | matched_patterns 
------------ | ---------------- 
1 chaining   | %chain%          
2 fullfill   | %full%           
3 full chain | %chain%,%full%   
4 no pattern | 

小提琴

mcnets：你不能使用：

SELECT CASE WHEN CONTAINS(Name, '"chain*") THEN 'chain' ELSE 'full' END

---

秋名：

CASE
   WHEN CONTAINS(Name, '"chain*") AND NOT CONTAINS(Name, '"full*") THEN '"chain"'
   WHEN CONTAINS(Name, '"full*") AND NOT CONTAINS(Name, '"chain*") THEN '"full"'
   ELSE 'both chain and full'
END

看起來更有資訊量。

引用自：https://dba.stackexchange.com/questions/244307