与 SQL 中的 LIKE 条件匹配的空字符串变量 [英] Empty string variable matching to LIKE conditions in SQL
问题描述
我有一个包含 nvarchar(max) 列的 SQL Server 表.我运行了一个查询来选择该列包含字符串remove"的每一行.在结果中，我有几行在该列中填充了一个空字符串，但我不明白为什么返回了这些值.
I have a SQL Server table containing a nvarchar(max) column. I ran a query to select every row where this column contains the string 'remove'. Amongst the results, I have several rows where this column is populated with an empty string and I don't understand why these values have been returned.
我试图对返回的空字符串值之一进行进一步调查.我发现空字符串值会匹配某些 LIKE 条件，但不会匹配其他条件.例如，它会匹配 %remove% 和 %x%，但不会匹配 %q%.
I tried to investigate further, on one of the empty string values returned. I found that the empty string value would match to certain LIKE conditions, but not others. For instance, it will match to %remove% and %x%, but it won't match to %q%.
在 SQL Server Management Studio 的结果网格中，nvarchar 字段显示为空字段(不为空).但是，如果我将 len() 函数应用于该字段，它会返回 649 的值.当我将结果表从 SQL Server Management Studio 导出到 Excel 时，nvarchar(max) 字段为空.
Within the results grid on SQL Server Management Studio, the nvarchar field is displayed as an empty field (it is not null). However, if I apply the len() function to this field, it returns the value of 649. When I export the results table from SQL Server Management Studio into Excel, the nvarchar(max) field is empty.
假设必须有一堆空白字符填充该字段，我尝试在将 'x' 连接到返回值的任一端后选择该字段.在结果中，前导 'x' 出现在字符串的开头，但不显示连接到末尾的 'x'.
Assuming that there must be a bunch of whitespace characters populating the field, I tried to select the field after concatenating 'x' to either end of the value returned. In the results, the leading 'x' appears at the beginning of the string, but the 'x' concatenated onto the end is not displayed.
我完全不知道发生了什么.我可以应要求提供更多信息.
I'm totally confused as to what is going on. I can provide more information upon request.
select itemid, content
from objects
where itemid = 100 and content like '%remove%'
----------------
itemid | content
100 |
select itemid, 'x' + content + 'x' as 'content'
from objects
where itemid = 100 and content like '%remove%'
----------------
itemid | content
100 | x
修剪字段不会改变字符数.但是，当我确实使用子字符串选择连接输出中的最后一个字符时，返回的字符是x"，这表明由于字符串的长度，它在 GUI 输出中不可见.但是，在将内容导出到 Excel 时，我仍然看不到这个尾随的x"字符.我认为 nvarchar 变量中可能存在不受支持的字符.
Trimming the field doesn't change the character count. However, when I do use substring to select the last character in the concatenated output, the character returned is 'x', which suggests that it just isn't visible in the GUI output due to the length of the string. I still can't see this trailing 'x' character when exporting the content into Excel though. I think that there are maybe unsupported characters present within the nvarchar variable.
select itemid, 'x' + ltrim(rtrim(content)) + 'x' as 'content'
from objects
where itemid = 100 and content like '%remove%'
----------------
itemid | content
100 | x
推荐答案
我猜你在字符串中有一些控制字符.例如，如果第一个字符是换行符，则可能会导致其余字符不显示.
My guess is that you have some control character(s) in the string. If the first character is a newline, for example, that may cause the rest not to be displayed.
试试
select item_id, unicode(content)
from objects
where item_id = 100;
这将显示您可以看到第二个字符的第一个字符的 unicode 代码点:
This will show the unicode code point for the first character you can see the second character with:
unicode(substring(item, 2, 1))
等等.
这篇关于与 SQL 中的 LIKE 条件匹配的空字符串变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
