4000 Character Limit In Like Statement

I have been getting an error in a previously working stored procedure called by an SSRS report and I have traced it down to a LIKE statement in a scalar function that is called by

Solution 1:

You can also reproduce it without the terrible loop:

DECLARE@name1 NVARCHAR(MAX), @name2 NVARCHAR(MAX);

SET@name1= REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 4000);
SET@name2=@name1;

IF @name1LIKE@name2
  PRINT 'OK';

SELECT@name1+= N'a', @name2+= N'a';

IF @name1LIKE@name2
  PRINT 'OK';

Result:

OK Msg 8152, Level 16, State 10, Line 30 String or binary data would be truncated.

In any case, the reason is clearly stated in the documentation for LIKE (emphasis mine):

match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ] ... 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.

And 8,000 bytes is used up by 4,000 Unicode characters.

I would suggest that comparing the first 4,000 characters is probably sufficient:

WHEREcolumnLIKELEFT(@param, 4000) +'%';

I can't envision any scenario where you want to compare the whole thing; how many strings contain the same first 4000 characters but then character 4001 is different? If that really is a requirement, I guess you could go to the great lengths identified in the Connect item David pointed out.

A simpler (though probably much more computationally expensive) workaround might be:

IF CONVERT(VARBINARY(MAX), @name1) =CONVERT(VARBINARY(MAX), @name2)
  PRINT 'OK';

I suggest that it would be far better to fix the design and stop identifying rows by comparing large strings. Is there really no other way to identify the row you're after? This is like finding your car in the parking lot by testing the DNA of all the Dunkin Donuts cups in all the cup holders, rather than just checking the license plate.

Solution 2:

I have the same problem right now, and I do believe my situation -where you want to compare two strings with more than 4000 characters- is a possible situation :-). In my situation, I'm collecting a lot of data from different tables in a NVARCHAR(MAX) field in a specific table, to be able to search on that data using FullText. Keeping that table in sync, is done using the MERGE statement, converting everything to NVARCHAR(MAX). So my MERGE statement would look like this:

MERGE MyFullTextTable AS target  
    USING (
        SELECT--Various stuff from various tables, casting it as NVARCHAR(MAX)
            ...
        ) AS source (IndexColumn, FullTextColumn)  
    ON (target.IndexColumn = source.IndexColumn)  
WHEN MATCHED AND source.FullTextColumn NOTLIKE target.FullTextColumn THENUPDATESET FullTextColumn = source.FullTextColumn
WHENNOT MATCHED THENINSERT (IndexColumn, FullTextColumn)  
    VALUES (source.IndexColumn, source.FullTextColumn)
    OUTPUT -- Some stuff

This would produce errors because of the LIKE comparison when the FullText-data is bigger than 4000 characters.

So I created a function that does the comparison. Allthough it's not bullet proof, it works for me. You could also split data in blocks of 4000 characters, and compare each block, but for me (for now) comparing the first 4000 characters in combination with the length, is enough ...

So the Merge-statement would look like:

MERGE MyFullTextTable AS target  
    USING (
        SELECT--Various stuff from various tables, casting it as NVARCHAR(MAX)
            ...
        ) AS source (IndexColumn, FullTextColumn)  
    ON (target.IndexColumn = source.IndexColumn)  
WHEN MATCHED AND udfCompareTwoTexts(source.FullTextColumn, target.FullTextColumn) =1THENUPDATESET FullTextColumn = source.FullTextColumn
WHENNOT MATCHED THENINSERT (IndexColumn, FullTextColumn)  
    VALUES (source.IndexColumn, source.FullTextColumn)
    OUTPUT -- Some stuff

And the function looks like:

ALTERFUNCTION udfCompareTwoTexts
(
    @Value1AS NVARCHAR(MAX),
    @Value2AS NVARCHAR(MAX)
)
RETURNS BIT
ASBEGINDECLARE@ReturnValueAS BIT =0
    IF LEN(@Value1) >4000OR LEN(@Value2) >4000BEGIN
            IF LEN(@Value1) = LEN(@Value2) ANDLEFT(@Value1, 4000) LIKELEFT(@Value2, 4000)
                SET@ReturnValue=1ELSESET@ReturnValue=0ENDELSEBEGIN
            IF @Value1LIKE@Value2SET@ReturnValue=1ELSESET@ReturnValue=0ENDRETURN@ReturnValue;
END
GO