I have a list with searchitems:

DROP TABLE IF EXISTS #searchitems

SELECT 'Road' as  item
INTO #searchitems
UNION 
SELECT 'Bike'

And I want looking for rows that contain that items

I put a Full Text Index on the Name column and I tried this already

SELECT Name
FROM [AdventureWorksLT2019].[SalesLT].[Product]
WHERE CONTAINS(*, (Select item FROM #searchitems) )

But it does not work. With just one value its working but not with a list of search values. Does it even possible with CONTAINS on a SQL-Server.

I expect something like this:

SELECT distinct Name
FROM [AdventureWorksLT2019].[SalesLT].[Product]
WHERE CONTAINS(*, 'Road OR Bike' )

expected output, but why its not working on a input list

CodePudding user response：

Refere the documentation https://learn.microsoft.com/fr-fr/sql/t-sql/queries/contains-transact-sql?view=sql-server-ver16

SELECT distinct Name
FROM [AdventureWorksLT2019].[SalesLT].[Product]
WHERE CONTAINS(*, 'Road OR Bike' )

Is working, official example:

SELECT Name  
FROM Production.Product  
WHERE CONTAINS(Name, ' Mountain OR Road ')  

Update

maybe that's your solution :

SELECT Name
FROM [AdventureWorksLT2019].[SalesLT].[Product]
WHERE CONTAINS(*, (Select STRING_AGG(item, ' OR ') FROM #searchitems) 

CodePudding user response：

One way is to use a CTE and JOIN it to your table with LIKE:

WITH searchitems AS
(SELECT 'Road' AS item
UNION ALL
SELECT 'Bike')
SELECT DISTINCT y.name
FROM yourtable y
JOIN searchitems s
ON y.name LIKE CONCAT('%',s.item,'%');

In the CTE, you can select as many items as desired.

You should generally note that lots of LIKE conditions can slow down your query if your table holds very many entries (and of course, similar ideas like CONTAINS can be very slow, too).

This should be avoided if possible and be replaced with an exact string search to improve the execution time.

Then of course the way shown above is no more required, you could just use a standard IN clause:

SELECT name
FROM yourtable
WHERE 
name IN ('Road','Bike','Hello World','Another Text');

Try out with some sample data: db<>fiddle