How can I perform an 'AND' search across multiple full text index tables in my SQL Server command?

Question

I'm currently using SQL Server 2016 and I need to search for n-number of user input terms across four different full-text index tables. We originally tried this:

DECLARE @searchString varchar(1000)
DECLARE @searchText varchar(1000)

SET @searchText = 'black schwinn bike'  --input by user

SET @searchString = 'FORMSOF(INFLECTIONAL, "' + RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@searchText,' AND ',','), ' OR ',','),' & ',','),' ',','),',','") OR FORMSOF(INFLECTIONAL,"'))) + '")'                                                 
--'FORMSOF(INFLECTIONAL, "black") OR FORMSOF(INFLECTIONAL,"schwinn") OR FORMSOF(INFLECTIONAL,"bike")'

SELECT DISTINCT 
    COALESCE(ftProduct.ProductKey,ftBrand.BrandKey, ftOption.OptionKey) AS KeyFound, 
    COALESCE(ftProduct.ProductRank, ftBrand.BrandRank, ftOption.OptionRank) AS Rank
FROM 
    (
        SELECT 
            [KEY] AS ProductKey, 
            [RANK] AS ProductRank
        FROM CONTAINSTABLE(tbProducts,*, @searchString )                    
     ) AS ftProduct
FULL OUTER JOIN
    (
        SELECT 
            [KEY] as BrandKey,
            [RANK] AS BrandRank
        FROM CONTAINSTABLE(tbBrands,*, @searchString)       
     ) AS ftBrand
     ON  ftProduct.ProductKey=ftBrand.BrandKey
FULL OUTER JOIN
    (
        SELECT 
            [KEY] AS OptionKey, 
            [RANK] AS OptionRank
        FROM CONTAINSTABLE(tbOptions,*, @searchString)      
     ) AS ftOption
     ON  ftProduct.ProductKey=ftOPtion.OptionKey

This worked when the user wanted to do an OR search but we need it to do an AND search. The All-inclusive search has turned out to be much more difficult. If I change the OR to AND when I build the @searchString this will only find records where all 3 terms are found in the same table. If I keep @searchString as OR but add an AND condition to the main SELECT, then it's possible it's finding the 3 terms in all of the tables but it may also be finding the same term in multiple tables. CONTAINSTABLE only returns Key and Rank, it does not tell me which of the 3 terms was found in the table. So, I have no way of knowing if all 3 terms were found or if just one or two of the terms was found multiple times in different tables.

Finally, I realized that the only way to be sure I am finding at least one term across all 3 tables is to search each term separately. I used a CTE to build a table of the provided search terms, then joined that to the result of CONTAINSTABLE. This is a simplified example of what I tried.

DECLARE @searchText varchar(1000)    
SET @searchText = 'black schwinn bike'

DECLARE @searchTerms TABLE (Term varchar(100))
INSERT INTO @searchTerms (Term)
SELECT value FROM STRING_SPLIT(@searchText, ' ')

;WITH searchConditions AS (
    SELECT 
        Term AS searchTerm,
        'FORMSOF(INFLECTIONAL, "' + Term + '")' AS searchCondition
    FROM @searchTerms
) 
    SELECT 
        [KEY] AS ProductKey,
        [RANK] AS ProductRank
    FROM searchConditions AS sc
        CROSS APPLY CONTAINSTABLE(tblRL_Products, *, sc.searchCondition )        
        --CROSS APPLY CONTAINSTABLE(tblRL_Products, *, 'FORMSOF(INFLECTIONAL, "black")' )

This is throwing a syntax error because it does not want me passing in the sc.searchCondition into the CONTAINSTABLE function. It will only take a straight string value and not a table referenced value. If you uncomment out the CROSS APPLY beneath it to swap those lines it runs fine. I believe this is a limitation of CONTAINSTABLE.

I can't seem to find a solution to search for multiple terms across multiple full-text index tables while making sure each term appears in at least one of the tables. I'm hoping someone can help me with a different solution or a workaround for the issue with CONTAINSTABLE not accepting a value from the JOINED table.

I also tried this solution (stackoverflow.com/questions/40378070/…). However, I need a LEFT JOIN and SQL will now allow me to index a view with an OUTER JOIN, with a CTE, a derived table, or a view referencing another view. It seems impossible for me to get a full text index on a view that has to have a left join in it. — LarryG
– LarryG, Commented Feb 18 at 17:05
I don't have full text running but you could create a couple of TVF functions (non inline) which accepts a single term and returns a containstable output — siggemannen
– siggemannen, Commented Feb 27 at 10:30

Charlieface · Accepted Answer · 2025-02-18 10:35:49Z

1

You could use dynamic SQL for this. The following script will do this for you.

We union all the results together, then group by KEY and only include grouped results where the number of unique searchTerm values is equal to the number of search terms we started with.

DECLARE @searchText nvarchar(1000) = 'black schwinn bike';

DECLARE @sql nvarchar(max);

DECLARE @searchTerms TABLE (Term varchar(100))
INSERT INTO @searchTerms (Term)
SELECT value FROM STRING_SPLIT(@searchText, ' ');

SET @sql = N'
WITH searches AS (' + (

SELECT STRING_AGG(CONVERT(nvarchar(max), N'
    SELECT
        ' + QUOTENAME(Term, '''') + N' AS searchTerm,
        [KEY],
        RANK
    FROM CONTAINSTABLE(tblRL_Products, *, ' + QUOTENAME('FORMSOF(INFLECTIONAL, ' + QUOTENAME(Term, '"') + ')', '''') + ' )
    UNION ALL
    SELECT
        ' + QUOTENAME(Term, '''') + N' AS searchTerm,
        [KEY],
        RANK
    FROM CONTAINSTABLE(tbBrands, *, ' + QUOTENAME('FORMSOF(INFLECTIONAL, ' + QUOTENAME(Term, '"') + ')', '''') + ' )
    UNION ALL
    SELECT
        ' + QUOTENAME(Term, '''') + N' AS searchTerm,
        [KEY],
        RANK
    FROM CONTAINSTABLE(tbOptions, *, ' + QUOTENAME('FORMSOF(INFLECTIONAL, ' + QUOTENAME(Term, '"') + ')', '''') + ' )
'), '
    UNION ALL
')
FROM @searchTerms

) + N'
)
SELECT
  [KEY] AS KeyFound,
  AVG(1.0 * RANK) AS Rank
FROM searches
GROUP BY
  [KEY]
HAVING COUNT(DISTINCT searchTerm) = ' + CONVERT(nvarchar(30), (SELECT COUNT(*) FROM @searchTerms));

PRINT @sql;   -- your friend

EXEC sp_executesql @sql;     -- add parameters if necessary

answered Feb 18 at 10:35

Charlieface

78.5k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

LarryG Feb 18 at 16:32

This is definitely possible and we considered this route but we're really trying to avoid dynamic SQL. This is the most used query in our entire database and between efficiency and security we try to avoid dynamic SQL at all costs. Although If these are not your limitations, this is a good solution.

Charlieface Feb 18 at 17:47

Dynamic SQL can be more efficient depending on the situation, and security should be fine as long as you escape correctly with QUOTENAME or similar. Dynamic SQL is less efficient when parameterization plays a big part, but in the case of Full Text Search it makes no difference.

Thomas Koelle Feb 19 at 9:44

Couldn't this solution be made without dynamic SQL but just UNION?

Charlieface Feb 19 at 11:51

If you knew how many terms there are and split them up beforehand then yes, but here we don't know and are using STRING_SPLIT

LarryG Feb 19 at 21:05

I've simplified the stored procedure in my example, there are a lot more parameters than just these keywords and this would cause efficiency issues with dynamic SQL. The UNION would have the same issues as the OUTER JOINS, you still can't search across all 4 indexes at once. I am working on another solution creating a compound field with an indexed view and I will update this if it works.

Collectives™ on Stack Overflow

How can I perform an 'AND' search across multiple full text index tables in my SQL Server command?

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related