Multiple LIKE statements in TSQL

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!

---

下面的查询应该这样做，但它可能不是最快的！

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
    )

---

If all else fails, you can run a cursor over @SearchTerms and loop over the results, checking for each item if it's present in your results.

You most likely won't have many search items (5-6 would be so rare), so the cursor cost should be negligible compared to running like over and over on your text. And that shouldn't be too expensive either.

Edit: What I've done in the past for searches is send the query in plain text to the server instead of relying on stored procedures, so I could piece together my conditions myself. You can do the same with dynamic queries and exec on the server if you so wish. This way seems pretty hacky and it's not obvious it's a performance improvement. Food for thought.

链接地址: http://www.djcxy.com/p/58988.html

上一篇: 解释/ proc / [id] / mountstats或/ proc / self / mountstats中的数据

下一篇: TSQL中的多个LIKE语句