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'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.
Share Improve this question asked Feb 17 at 19:09 LarryGLarryG 6971 gold badge7 silver badges17 bronze badges 1- I also tried this solution (stackoverflow/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 Commented 2 days ago
1 Answer
Reset to default 1You 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