Multiple LIKE statements in TSQL

sql-like, sql-server, t-sql, user-defined-functions

Solution

The query 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
    )

Problem

This is what I'm trying to achieve in it's 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'm trying to do this in a UDF, which accepts a comma separated parameter of all the search terms. I tried splitting that parameter into a temporary table and trying a join, like this: ``` 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 that doesn't return what I want - it returns any records where the Name or SKU matches either of the search terms. I need it to return like the first query, where the Name or SKU matches all of the search terms (I think that makes sense). Would be massively appreciative of a push in the right direction - let me know if you need me to be more specific. Note: full text searching is not a viable option at the moment. Thanks!

Original source