a recursive string compare

Question

Im not very strong on loops and recursive queries in T-SQL (might as well given that it's more of a set based language anyway) but I have still problem which I can't figure out without the use of recursive query and a function?

I have 2 tables called Brands and Products.

CREATE TABLE Brands (RowID INT, BrandName VARCHAR(50))
CREATE TABLE Products (RowID Int, ProductName VARCHAR(50), BrandName VARCHAR(50))

So basically, I need to check each row in the Products table against the Brands table to see if the BrandName appears in the ProductName in the Products table. And if so, update the BrandName field in the products table.

Thanks.

Answer 1

As far as I can tell there's no need for recursion or loops to do this:

UPDATE Products
SET BrandName = 'updated value'
FROM Products AS p
    INNER JOIN Brands AS b
        ON p.ProductName LIKE '%' + b.BrandName + '%'