SQL通配符不返回数据？

发布于 2024-09-12 16:25:18 字数 415 浏览 6 评论 0原文

我有一个通过存储过程在 gridview 中使用的 select 语句：

SELECT * FROM T_Computer WHERE (ISTag LIKE '%' + @ISTag + '%') AND Status <> 'Trashed' AND Status <> 'Sold' AND Status <> 'Stored'

@ISTag，一个 nchar 由文本框值确定，如果文本框为空，我将默认值设置为 %，在我看来，这基本上应该否定where 子句的该部分并返回除 Status 列否定的行之外的所有行。相反，它没有返回任何内容。如果我删除“+ @ISTag +”，它就会执行我想要的操作。

基本上 3% 不会返回任何内容，但 2% 会返回我想要的内容。我应该如何解决这个问题？

原文

I have a select statement that is used in a gridview via a stored procedure:

SELECT * FROM T_Computer WHERE (ISTag LIKE '%' + @ISTag + '%') AND Status <> 'Trashed' AND Status <> 'Sold' AND Status <> 'Stored'

The @ISTag, an nchar is determined by a textbox value, if the textbox is empty I have the default value set to %, which in my mind should essentially negate that part of the where clause and return all of the rows except the ones negated by the Status column. Instead it isn't returning anything. If I remove the "+ @ISTag +" it does what I want.

Basically three %'s return nothing, but 2 %'s return what I want. How should I fix this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

凶凌 2024-09-19 16:25:18

这是一种替代方案，将 @ISTag 参数的默认值保留为 NULL

SELECT * 
FROM T_Computer 
WHERE (@ISTag IS NULL OR (ISTag LIKE '%' + @ISTag + '%')) 
AND Status <> 'Trashed' 
AND Status <> 'Sold' 
AND Status <> 'Stored'

Here is an alternative, leaving the default value of the @ISTag parameter to NULL

SELECT * 
FROM T_Computer 
WHERE (@ISTag IS NULL OR (ISTag LIKE '%' + @ISTag + '%')) 
AND Status <> 'Trashed' 
AND Status <> 'Sold' 
AND Status <> 'Stored'

回复收藏 0 原文

物价感观 2024-09-19 16:25:18

我对此非常感兴趣。首先，我对一个包含 13221 行的表运行这两个查询，并在第一个实例中获取每一行，在第二个实例中获取大约一半的行：

declare @rad char(30)
select @rad = '%'

select count(*) from xofdoc where docdesc like '%%%'
select count(*) from xofdoc where docdesc like '%' + @rad + '%'

另一方面，如果我将 @rad 的声明更改为 < strong>varchar(30)，两个查询都会提取所有行。

我提出这个问题的原因是因为您的 @IsTag 的数据声明可能发生了一些问题。你没有说它是如何声明的。这很重要，因为 char 是固定长度的字符串，这意味着将 char(5) 设置为 '%' 的值，实际上将具有值 '% '< /代码>。因此，语句 '%' + '% ' + '%' 的计算结果为：'%% %'。因此它只会找到在 whered 列中至少有四个连续空格的行。另一方面，varchar 是可变长度项，并且会忽略尾随空格，这会导致在上述情况下出现 '%%%'，从而对整个列进行通配符。

编辑添加：nchar或char，在这种情况下没有区别。如果您现在使用 nchar，请将其更改为 nvarchar。

I'm really intrigued by this. For a start, I ran these two queries against a table with 13221 rows, and got every single row in the first instance, and about half of them in the second:

declare @rad char(30)
select @rad = '%'

select count(*) from xofdoc where docdesc like '%%%'
select count(*) from xofdoc where docdesc like '%' + @rad + '%'

ON THE OTHER HAND, if I change the declaration of @rad to varchar(30), both queries pull all the rows.

The reason I bring this up is because there may be something going on with the data declaration of your @IsTag. You didn't say how it was declared. This is important because a char is a fixed-length string, meaning that a char(5) for example, set to the value of '%' will actually have the value '% '. So, the statement '%' + '% ' + '%' evaluates to: '%% %'. So it would only find rows which had at least four consecutive spaces in the whered column. On the other hand, a varchar is a variable length item, and trailing spaces are ignored, which results in '%%%' in the above case, and thus wildcards the entire column.

Edited to add: nchar or char, it makes no difference in this case. If you're using nchar now, change it to nvarchar.

回复收藏 0 原文