Click here to Skip to main content
15,888,461 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Seems not...

Imagine a table like this:
SQL
FORUM_MESSAGES (
  ID INT,
  COMMENT NVARCHAR(MAX)
)

Now see this query:
SQL
DECLARE @LIKE AS NVARCHAR(MAX) = NULL

SELECT
	*
FROM
	FORUM_MESSAGES
WHERE
	COMMENT LIKE ISNULL(@LIKE, COMMENT)

If you have a COMMENT longer than 4000 letters it will drop a truncate error...
Looking at the execution plan I found this little change:
SQL
FORUM_MESSAGES.COMMENT like CONVERT_IMPLICIT(NVARCHAR(4000), ISNULL(@LIKE, FORUM_MESSAGES.COMMENT ), 0)

After some digging it became obvious that the LIKE operator does that to the SQL...
Looked around in the internet, but found no confirmation of that behavior...
Have you had any experience with LIKE and NVARCHAR(MAX)?

[UPDATE]
As to clear the air following OG's solution...
I do understand why LIKE does CONVERT... but there is the problem, that changing @LIKE to NVACHAR(4000) does not solve the problem...
In that case there is no truncate error (even SQL does run a CONVERT_IMPLICIT, now on COMMENT), but rows with more than 4000 letters will be removed without error... Somehow it is even worst than before as now there is no error, while clearly there is a truncation...

What I have tried:

Anything in my power...
Talk to DBA...
Intensive Google...

Actually found some solutions... but I looking for an explanation why SQL's LIKE forces me down to NVARCHAR(4000)...
Posted
Updated 29-May-18 2:51am
v2
Comments
Dave Kreskowiak 29-May-18 8:35am    
Why on earth are you using a LIKE expression with a parameter that damn big?
Kornfeld Eliyahu Peter 29-May-18 8:45am    
It is a kind of legacy I've got...
With the error...
Part of my solution is no MAX...
Dave Kreskowiak 29-May-18 9:25am    
You inherited this mess? You have my sympathies. :)
Kornfeld Eliyahu Peter 29-May-18 9:56am    
Mine toooooooooo :-)

It's in the documentation: LIKE (Transact-SQL) | Microsoft Docs[^]
Quote:
Arguments
match_expression
Is any valid expression of character data type.

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.

Since NVARCHAR uses Unicode 16 bit characters, that's a 4,000 character max.
 
Share this answer
 
Comments
Kornfeld Eliyahu Peter 29-May-18 8:44am    
Sorry, but I wasn't clear on this...
Change @LIKE to NVARCHAR(4000) and the error's gone, but the culprit (the record with more than 4000 letters in COMMENT) will not show...
Now SQL does a CONVERT_IMPLICIT on COMMENTS, but not it is all right and no truncate error, but no record either...
OriginalGriff 29-May-18 9:02am    
That doesn't really surprise me that much: if Comment has more than 4000 characters, and is truncated by an implicit convert, then unless the 4000th character is '%' the pattern won't match. I'd be tempted to change the whole condition:
... WHERE @LIKE IS NULL OR Comment LIKE @LIKE
Kornfeld Eliyahu Peter 29-May-18 9:06am    
An the fact that there is a truncation (in the specific case triggered the error it is a truncation of 21000 to 4000) of COMMENT, but there is no error, does not surprise you either?
OriginalGriff 29-May-18 9:31am    
No, because there is no "loss of data", since WHERE clauses don't alter anything - and SQL isn't worried about trivia unless it could compromise integrity. :laugh:
Kornfeld Eliyahu Peter 29-May-18 9:56am    
That's what I was thinking... Because the expression created by the truncate not part of the result set, SQL does no care...
For LIKE (Transact-SQL) | Microsoft Docs[^] it is documented:
Quote:
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.

It might be defined in the official SQL standard. Because you have to pay for that, you probably did not found much in the web.
 
Share this answer
 
Comments
Kornfeld Eliyahu Peter 29-May-18 8:51am    
See comments for OG and the update of the question...
Jochen Arndt 29-May-18 8:58am    
So you are using T-SQL?
Because even when defined by the standard I guess it is database provider specific; especially the behaviour of getting no error now.
Kornfeld Eliyahu Peter 29-May-18 9:00am    
It is T-SQL...
I do not know about provider configuration that will hide one truncation error, but show the other on the same query...
I think the second truncation error is swallowed because it is done on an expression and that expression never part of the final result...
Jochen Arndt 29-May-18 9:19am    
With provider I mean the DB engine (T-SQL, MySQL, PostgreSQL) which might behave differently.

I did not expect that there is a configuration option for such errors.

Overall, I'm sorry that I can't help more. It might be one of those problems that can't get finally solved.
Kornfeld Eliyahu Peter 29-May-18 9:54am    
I thank you for any help!!!
Actually the problem solved, but not explained fully...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900