Geek Answers Handbook

Multiple LIKE statements in TSQL

This is what I am trying to achieve in its simplest form:

SELECT p.ProductId, p.ProductName, p.SKU FROM tbl_Product p WHERE (p.ProductName LIKE '%white%' OR p.SKU LIKE '%white%') AND (p.ProductName LIKE '%cup%' OR p.SKU LIKE '%cup%')

I am trying to do this in UDF, which accepts a comma separated parameter for all search terms.

I tried to split this parameter into a temporary table and try the connection, for example:

 DECLARE @SearchText nvarchar(1000) SELECT @SearchText='white,cup' DECLARE @SearchTerms TABLE (String nvarchar(200)) INSERT INTO @SearchTerms (String) SELECT '%' + String + '%' FROM dbo.CsvSplitString(@SearchText) SELECT p.ProductId, p.ProductName, p.SKU FROM tbl_Product p JOIN @SearchTerms s ON (p.ProductName LIKE s.String OR p.SKU LIKE s.String)

But this does not return what I want - it returns any records in which the name or SKU matches one of the search conditions. I need it to be returned as the first query, where the name or SKU matches all search queries (I think that makes sense).

It would be great thanks for pushing in the right direction - let me know if you need me to be more specific.

Note: Full-text search is currently not a viable option.

Thanks!

+7
source share
2 answers

The request below should do it, but it may not be the fastest!

 DECLARE @SearchText nvarchar(1000) SELECT @SearchText='white,cup' DECLARE @keywords TABLE (keyword nvarchar(255)) DECLARE @keywordCount int INSERT INTO @keywords (keyword) SELECT * FROM dbo.CsvSplitString(@SearchText) SET @keywordCount = (SELECT COUNT(*) FROM @keywords) SELECT * FROM tbl_Product p WHERE EXISTS (SELECT * FROM (SELECT productId FROM tbl_Product, @keywords WHERE productname like '%' + keyword + '%' or sku like '%' + keyword + '%' GROUP BY productid HAVING COUNT(*) = @keywordCount ) matches WHERE p.ProductId=matches.ProductId )
+4
source

If all else fails, you can start the cursor over @SearchTerms and @SearchTerms over the results, checking each element if it is present in your results.

You most likely will not have many search elements (5-6 will be so rare), so the cost of the cursor should be negligible compared to running like over and over in your text. And this also should not be too expensive.

Edit: what I did in the past for searching sends a text request to the server instead of relying on stored procedures, so I could collect my conditions myself. You can do the same with dynamic queries and exec on the server if you want. This method seems pretty hacky, and it’s not obvious, it’s a performance improvement. Food for thought.

0
source

More articles:


All Articles