CODEWITHSUNDEEP
sqlstored-proceduresgridviewwildcard
Shawn
Shawn

Reputation: 2366

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 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?

Upvotes: 2

Views: 2849

Answers (4)

Cyberherbalist
Cyberherbalist

Reputation: 12309

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.

Upvotes: 3

Dustin Laine
Dustin Laine

Reputation: 38503

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'

Upvotes: 4

Mike Marshall
Mike Marshall

Reputation: 7850

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.

Upvotes: -1

lincolnk
lincolnk

Reputation: 11238

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 %%%.

Upvotes: 0

Related Questions