CODEWITHSUNDEEP
sql-server
Orion
Orion

Reputation: 452

SQL Where Clause with Contains

I am using NopCommerce 3.5 and am trying to modify the SQL Stored Procedure to be able to search SKU

My Question is how do I get the following SQL to also check if the Column Value is also contained in the Keywords? 

--SKU
    IF @SearchSku = 1
    BEGIN
        SET @sql = @sql + '
        UNION
        SELECT p.Id
        FROM Product p with (NOLOCK)
        WHERE '
        IF @UseFullTextSearch = 1
            SET @sql = @sql + '(CONTAINS(p.[Sku], @Keywords) OR CONTAINS(@Keywords, p.[Sku])) '
        ELSE
            SET @sql = @sql + '(PATINDEX(@Keywords, p.[Sku]) > 0 OR PATINDEX(p.[Sku], @Keywords) > 0) '
    END

Upvotes: 0

Views: 394

Answers (2)

Orion
Orion

Reputation: 452

Xedni's comment above helped solved my issue. Changed the SQL Statement to the following.

--SKU
    IF @SearchSku = 1
    BEGIN
        SET @sql = @sql + '
        UNION
        SELECT p.Id
        FROM Product p with (NOLOCK)
        WHERE '
        IF @UseFullTextSearch = 1
            SET @sql = @sql + '(CONTAINS(p.[Sku], @Keywords) OR CONTAINS(@Keywords, ''%'' + p.[Sku] + ''%'')) '
        ELSE
            SET @sql = @sql + '(PATINDEX(@Keywords, p.[Sku]) > 0 OR PATINDEX(''%'' + p.[Sku] + ''%'', @Keywords) > 0) '
    END

Upvotes: 0

Xedni
Xedni

Reputation: 4695

try wrapping p.sku with wildcards. e.g. patindex('%' + p.[sku] + '%', @keywords)

Upvotes: 1

Related Questions