Sql-server – Get the matching condition in the SELECT list

full-text-searchsql serversql-server-2012string-searching

Let's say my full text search query is below:

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

I need one more column that shows which condition was matched in the CONTAINS clause.

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        |
+---------------------+-------------------+

What should I use to get the new column (matched_condition)?

I have a list of conditions that includes 200 words.

Best Answer

Possible realization. Fulltext search replaced with common LIKE substring search.

Source table:

CREATE TABLE test (val VARCHAR(16));

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

Patterns table (can be temporary or table-type variable):

CREATE TABLE patterns (pattern VARCHAR(8));

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

The query:

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;

Query result:

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

fiddle

Related Question