SQL wildcards not returning data?

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开发者_Python百科 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?

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'

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.

is your subsitute % mark being escaped? you wouldn't get what you epect if the search is matching a literal % character.

also, there's no need to use the % for an empty field. %% should return the same results as %%%.

The problem is that two consecutive percent symbols (%%) cause it to match the literal '%' character. So 3 consecutive mean "match a percent symbol and then any characters thereafter".

As LittleBobby indicates, do not use '%' as your default for nulls.