Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

The background to this question can be found here.

The following part of my stored procedure does not work. Is there a character length limitation for the LIKE clause? t1.ProductList sometimes can be up to 1000 characters in length:

DELETE t1 FROM #tmptable t1
WHERE EXISTS (SELECT 1 FROM #tmptable2_ t2
              WHERE t1.DealerId != t2.DealerId
              AND t2.ProductList LIKE '%' + t1.ProductList + '%'
              AND t2.numberOfVibs > t1.numberOfVibs);

Sample data for a single row of the ProductList column:


share|improve this question
What do you mean by 'does not work'? Do you get an error, no results without error or what else? Can you identify a row where it works and another one where not? – dezso Feb 15 '13 at 7:40
I dont get error but if i delete that part result is same. – OzanWt Feb 15 '13 at 7:43
Could you show some sample data? – dezso Feb 15 '13 at 7:44
@dezso Added content. – OzanWt Feb 15 '13 at 7:46
Is the value in t1.ProductList a comma separated list of values, or is it a normal table with one value per row? – mrdenny Feb 15 '13 at 16:19
up vote 7 down vote accepted

Is there any character length limitation for LIKE clause?

Yes. From the documentation for LIKE:


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';

sp_help output

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
            ',' + ProductCode 
        FROM Stocks
            s.SellerId = Stocks.SellerId
        ORDER BY
        FOR XML 
        , 1, 1, ''
FROM dbo.Stocks AS s
    s.ProductCode IN ('30A','20A','42B')
    AND s.StockData > 0

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

error message

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

FROM #SellerProducts AS t1
    SELECT 1 
    FROM #SellerProducts AS t2
        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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.