Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

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
1  
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
show 6 more comments

1 Answer 1

up vote 7 down vote accepted

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

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
(
    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:

error message

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.

share|improve this answer
add comment

Your Answer

 
discard

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.