LIKE character length limitation

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:

B1ZMA25706,B1ZMI09502,B1ZMI12910,B1ZMI18602,BB001TBL26,BBHMOVE4,BE09501FBL,BGS52200,BKS3003,BM2,BO11001EBO,BPGTB1200,BPGTC172HP,BPGTX663,BPIVMS6502,BPIVOD1022,BPVA475,BPVB1000,BPVB800,BPVC652,BPVPW1500,BPVW1000,BPVX652,BPVX662,BREMEN78,BSA2602,BSD2880,BSG62082,BSG71800,BSG81623,BSG82422,BSG82480,BSGL32015,CAB150,CAB21,CD1401B,CD21001WAL,CD21004WAL,CD2105WHI,CD2108WAL,CD5601S,CDBSE7300A,CEPM8CAPPU

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.

StackExchange Database Administrators Q#34803, answer score: 8