Sql-server – TSQL- Column DataType Length

decimal    binary
100     =  1100100
1       =  0000001
                     or
101     =  1100101

Is there any character length limitation for LIKE clause?

Yes. From the documentation for LIKE:

pattern

Is the specific string of characters to search for in match_expression, and can include the following valid wildcard characters. pattern can be a maximum of 8,000 bytes.

In your case the limit is 4,000 characters because the FOR XML PATH expression returns a Unicode string (two bytes per character). If you check the ProductList column of your temporary table, you will see the data type is nvarchar(max):

EXECUTE tempdb.sys.sp_help
    @objname = N'#tmptable';

Depending on your data, you may be able to use single-byte ANSI characters instead, giving you up to 7,998 characters for the concatenated string of ProductCodes:

CREATE TABLE #SellerProducts
(
    SellerID        integer PRIMARY KEY,
    ProductList     varchar(7998) NOT NULL,
    ProductCount    bigint NOT NULL
);

INSERT #SellerProducts
(
    SellerID, 
    ProductList, 
    ProductCount
)
SELECT
    s.SellerId,
    STUFF
    (
        (
        SELECT
            ',' + ProductCode 
        FROM Stocks
        WHERE
            s.SellerId = Stocks.SellerId
        ORDER BY
            ProductCode
        FOR XML 
            PATH('')
        )
        , 1, 1, ''
    ),
    COUNT_BIG(*)
FROM dbo.Stocks AS s
WHERE
    s.ProductCode IN ('30A','20A','42B')
    AND s.StockData > 0
GROUP BY
    s.SellerId;

The code above is deliberately designed to throw the following error if ProductList contains more than 7,998 characters:

You do not need to create a second copy of the temporary table to do the DELETE:

DELETE t1 
FROM #SellerProducts AS t1
WHERE EXISTS
(
    SELECT 1 
    FROM #SellerProducts AS t2
    WHERE
        t2.SellerId <> t1.SellerId
        AND t2.ProductList LIKE '%' + t1.ProductList + '%'
        AND t2.ProductCount > t1.ProductCount
);

The character limit for the ProductList column is 7,998 characters to allow two for the % characters added before the LIKE is performed - giving a total of 8,000 characters, the maximum allowed for the LIKE pattern string.