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
Best Answer
Yes. From the documentation for
LIKE
: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 theProductList
column of your temporary table, you will see the data type isnvarchar(max)
: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 ofProductCodes
: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
:The character limit for the
ProductList
column is 7,998 characters to allow two for the%
characters added before theLIKE
is performed - giving a total of 8,000 characters, the maximum allowed for theLIKE
pattern string.