SQL的LIKE是否喜欢NVARCHAR（MAX）？ [英] Does SQL's LIKE likes NVARCHAR(MAX)?
问题描述
似乎没有...
想象一下这样的表格：
Seems not...
Imagine a table like this:
FORUM_MESSAGES (
ID INT,
COMMENT NVARCHAR(MAX)
)
现在看到这个查询：
Now see this query:
DECLARE @LIKE AS NVARCHAR(MAX) = NULL
SELECT
*
FROM
FORUM_MESSAGES
WHERE
COMMENT LIKE ISNULL(@LIKE, COMMENT)
如果您的评论超过4000个字母，那么将删除截断错误...
查看执行计划我发现了这个小小的变化：
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:
FORUM_MESSAGES.COMMENT like CONVERT_IMPLICIT(NVARCHAR(4000), ISNULL(@LIKE, FORUM_MESSAGES.COMMENT ), 0)
经过一番挖掘后，很明显LIKE运算符对SQL做了这个......
在互联网上看了一下，但没有发现这种行为的确认...
你有过LIKE和NVARCHAR（MAX）的经历吗？
[更新]
清除在OG的解决方案之后空气......
我明白为什么LIKE会转换......但是有问题，将@LIKE改为NVACHAR（4000）并不能解决问题...
在这种情况下没有截断错误（即使SQL确实运行CONVERT_IMPLICIT，现在在COMMENT上），但超过4000个字母的行将被删除而不会出现错误......不知何故，它甚至比以前更糟糕了现在没有错误，而清除有一个截断...
我尝试过：
我的力量......
与DBA交谈...
强化Google ...
实际上找到了一些解决方案......但是我找到了解释为什么SQL的LIKE强迫我降级到NVARCHAR（4000）...
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)...
推荐答案
它在文档中： LIKE（Transact-SQL ）| Microsoft Docs [ ^ ]
It's in the documentation: LIKE (Transact-SQL) | Microsoft Docs[^]
参数
match_expression
是字符数据类型的任何有效表达式。
模式
是要在match_expression中搜索的特定字符串，并且可以包含以下有效通配符。 pattern最多可以包含8,000个字节。
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.
由于NVARCHAR使用Unicode 16位字符，最多为4,000个字符。
Since NVARCHAR uses Unicode 16 bit characters, that's a 4,000 character max.
对于 LIKE（Transact-SQL）| Microsoft Docs [ ^ ]记录：
For LIKE (Transact-SQL) | Microsoft Docs[^] it is documented:
pattern
是match_expression中要搜索的特定字符串，可以包含以下有效通配符。模式最多可以是8,000个字节。
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.
它可能在官方SQL标准中定义。因为你必须付钱，你可能在网上找不到多少。
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.
这篇关于SQL的LIKE是否喜欢NVARCHAR（MAX）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
