How to do partial match with a SELECT IN query?

Question

I'm not sure there is this exact situation in another question, so sorry if this a duplicate. I have a problem with the Category system I am using I have the following query (for example)

SELECT DISTINCT COUNT(StockID)
FROM tblStock
WHERE CategoryCode IN (
SELECT CategoryCode 
FROM tblLookup 
WHERE CategoryID = 'EG')

I need to do a partial match between the CategoryCode in tblLookup and the Category Code in tblStock - the query above returns full matches such as EG would have ETC and EGT as the Category codes returned however if the category is ETCE then this would not be found.
How could I modify the query so the results returned from:

SELECT CategoryCode 
FROM tblLookup 
WHERE CategoryID = 'EG'

Could be use for Partial Matching in tblStock, where the CategoryCodes returned are ETC and EGT but there are wildcard matches required also.

Answer 1

SELECT  COUNT(StockID)
FROM    (
        SELECT  DISTINCT '%' + CategoryCode + '%' AS Expr
        FROM    tblLookup
        WHERE   CategoryID = 'EG'
        ) cats
JOIN    tblStock s
ON      s.CategoryCode LIKE cats.Expr

A sample script to reproduce:

DECLARE @tblStock TABLE (StockID INT NOT NULL, CategoryCode VARCHAR(100) NOT NULL, quantity FLOAT NOT NULL)
DECLARE @tblLookup TABLE (CategoryID VARCHAR(100) NOT NULL, CategoryCode VARCHAR(100) NOT NULL)

INSERT
INTO    @tblStock
VALUES  (1, 'ETCE', 100)

INSERT
INTO    @tblStock
VALUES  (2, 'ETC', 200)

INSERT
INTO    @tblStock
VALUES  (3, 'FOO', 300)

INSERT
INTO    @tblLookup
VALUES  ('EG', 'ETC')

INSERT
INTO    @tblLookup
VALUES  ('EG', 'EGT')

SELECT  *
FROM    (
        SELECT  DISTINCT '%' + CategoryCode + '%' AS Expr
        FROM    @tblLookup
        WHERE   CategoryID = 'EG'
        ) cats
JOIN    @tblStock s
ON      s.CategoryCode LIKE cats.Expr