1

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.

0

2 Answers 2

2

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

0

mcnets: Can't you use:

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

akina:

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

looks more informative.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.