SQL Wildcard (%) query
-
Hey all, I am trying to construct a basic search query and am having trouble with my wildcard searches. I am using C# Visual Studio 2008 and the dataset query designer. The part of the query I am struggling with is:
WHERE (Land_use_code LIKE '%' +@Landusecode1 + '%')
However, when I run the query, it doesn't perform as I would expect. There are multiple table entries, with some as follows: R1 R3 R30 If I enter R and run the query, it returns nothing. If I enter R1, it returns all that match R1. If I enter R%, it will return R1 and R3, but not R30. If I enter R%%, I will get all records. I am wondering of a way to enable me to enter R% and get all records returned. Thanks, Joe
-
Hey all, I am trying to construct a basic search query and am having trouble with my wildcard searches. I am using C# Visual Studio 2008 and the dataset query designer. The part of the query I am struggling with is:
WHERE (Land_use_code LIKE '%' +@Landusecode1 + '%')
However, when I run the query, it doesn't perform as I would expect. There are multiple table entries, with some as follows: R1 R3 R30 If I enter R and run the query, it returns nothing. If I enter R1, it returns all that match R1. If I enter R%, it will return R1 and R3, but not R30. If I enter R%%, I will get all records. I am wondering of a way to enable me to enter R% and get all records returned. Thanks, Joe
I think there is a problem with the quotes. with a literal search, it would have to look like this:
WHERE Land_use_code LIKE '%R%'
so the entire literal string (with the percent signs if any) is inside quotes, as it is a string literal, not a numeric literal. I'm not sure how that works with a variable in SQL. FWIW: In C# it would be:
new SQLCommand("...WHERE (Land_use_code LIKE '%" + Landusecode1 + "%'");
BTW: you didn't mention what database was used; different databases might use different wildcard characters, and there could even be a character matching any single character, and another matching any sequence of characters. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
-
I think there is a problem with the quotes. with a literal search, it would have to look like this:
WHERE Land_use_code LIKE '%R%'
so the entire literal string (with the percent signs if any) is inside quotes, as it is a string literal, not a numeric literal. I'm not sure how that works with a variable in SQL. FWIW: In C# it would be:
new SQLCommand("...WHERE (Land_use_code LIKE '%" + Landusecode1 + "%'");
BTW: you didn't mention what database was used; different databases might use different wildcard characters, and there could even be a character matching any single character, and another matching any sequence of characters. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
Thanks Luc, I am using SQL Server 2008. So with your C# example there, you are passing in the variable Landusecode1 to the query and then applying the wildcards aren't you. If that is how I need to approach it then I can manage to sort it out, I just thought there might be an easier way (as you said - possibly a multiple character wildcard). Joe
-
Thanks Luc, I am using SQL Server 2008. So with your C# example there, you are passing in the variable Landusecode1 to the query and then applying the wildcards aren't you. If that is how I need to approach it then I can manage to sort it out, I just thought there might be an easier way (as you said - possibly a multiple character wildcard). Joe
Hi Joe, This page[^] suggests SQL variables can sit inside quotes (I know PHP does that), so it could be as simple as:
WHERE Land_use_code LIKE '%@Landusecode1%'
Beware the (absence of) spaces inside the quoted stuff. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
-
Hi Joe, This page[^] suggests SQL variables can sit inside quotes (I know PHP does that), so it could be as simple as:
WHERE Land_use_code LIKE '%@Landusecode1%'
Beware the (absence of) spaces inside the quoted stuff. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
From the look of it, the original
Land_use_code LIKE '%' + @Landusecode1 + '%
is the correct way. I might need to look into using combo boxes with equals|like in it to control the results a bit more, and code the query as necessary from those. Just a bit more than I wanted to have to do! But no doubt it will be good experience! Thanks for your help :-)
-
From the look of it, the original
Land_use_code LIKE '%' + @Landusecode1 + '%
is the correct way. I might need to look into using combo boxes with equals|like in it to control the results a bit more, and code the query as necessary from those. Just a bit more than I wanted to have to do! But no doubt it will be good experience! Thanks for your help :-)
Joe Stansfield wrote:
From the look of it, the original Land_use_code LIKE '%' + @Landusecode1 + '% is the correct way.
No way, you really need the percent signs and the known content all inside a single pair of quotes. Where is yous snippet sitting, inside a programming language or inside an SQL procedure? maybe show a bit more of it, and somebody will fix it for you. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
modified on Wednesday, December 22, 2010 7:41 AM
-
Joe Stansfield wrote:
From the look of it, the original Land_use_code LIKE '%' + @Landusecode1 + '% is the correct way.
No way, you really need the percent signs and the known content all inside a single pair of quotes. Where is yous snippet sitting, inside a programming language or inside an SQL procedure? maybe show a bit more of it, and somebody will fix it for you. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
modified on Wednesday, December 22, 2010 7:41 AM
It's sitting inside a datasource as a query (so similar to a stored procedure?) and then executed via a button and a table adapter in the programming language. This is the full query as it sits at the moment:
SELECT File_Name, PID, Address_1, Address_2, Locality, Municipality, Postcode, Sale_date, Sale_price, Total, Capital_value, Land_area, Land_use_code, Room_count, Building_area, Construction_year, Wall_construction_code, Roof_construction_code, Land, Rate_1, Val_Ref, Source FROM vSaleSearch
WHERE (Municipality = @Municiaplity1 OR Municipality = @Municipality2)
AND (Locality LIKE @Locality + '%' OR Locality = @Locality1 OR Locality = @Locality2)
AND (Land_use_code LIKE '%' + @Landusecode1 + '%')
AND (Sale_price >= @Sale_Price1 )
AND ( Sale_price <= @Sale_Price2)
AND (Sale_date >= @Sale_Date1)
ORDER BY Sale_priceI'm trying to achieve the same thing on the locality. What I'd ideally like is for a person to be able to enter a string in the locality field and it take it as an "equals" query. Or include a wild card at the start or end to take it as a like and apply the wild card characters. At the moment it achieves half of that, but not as user friendly as one might hope... I see what I want to happen all the time, but of course I'm not sure how exactly it is done...
-
It's sitting inside a datasource as a query (so similar to a stored procedure?) and then executed via a button and a table adapter in the programming language. This is the full query as it sits at the moment:
SELECT File_Name, PID, Address_1, Address_2, Locality, Municipality, Postcode, Sale_date, Sale_price, Total, Capital_value, Land_area, Land_use_code, Room_count, Building_area, Construction_year, Wall_construction_code, Roof_construction_code, Land, Rate_1, Val_Ref, Source FROM vSaleSearch
WHERE (Municipality = @Municiaplity1 OR Municipality = @Municipality2)
AND (Locality LIKE @Locality + '%' OR Locality = @Locality1 OR Locality = @Locality2)
AND (Land_use_code LIKE '%' + @Landusecode1 + '%')
AND (Sale_price >= @Sale_Price1 )
AND ( Sale_price <= @Sale_Price2)
AND (Sale_date >= @Sale_Date1)
ORDER BY Sale_priceI'm trying to achieve the same thing on the locality. What I'd ideally like is for a person to be able to enter a string in the locality field and it take it as an "equals" query. Or include a wild card at the start or end to take it as a like and apply the wild card characters. At the moment it achieves half of that, but not as user friendly as one might hope... I see what I want to happen all the time, but of course I'm not sure how exactly it is done...
When the query is a string built in a programming language (say C#), then this[^] seems to show the way. Basically, apply the wildcard characters (if you want them, this might depend on a CheckBox being checked) to the parameter before you set up the SQLParameter, then write your query without any quotes. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
-
Hey all, I am trying to construct a basic search query and am having trouble with my wildcard searches. I am using C# Visual Studio 2008 and the dataset query designer. The part of the query I am struggling with is:
WHERE (Land_use_code LIKE '%' +@Landusecode1 + '%')
However, when I run the query, it doesn't perform as I would expect. There are multiple table entries, with some as follows: R1 R3 R30 If I enter R and run the query, it returns nothing. If I enter R1, it returns all that match R1. If I enter R%, it will return R1 and R3, but not R30. If I enter R%%, I will get all records. I am wondering of a way to enable me to enter R% and get all records returned. Thanks, Joe
Add the percent signs to the parameter value.
parm.Value = "%" + foo + "%" -
Hey all, I am trying to construct a basic search query and am having trouble with my wildcard searches. I am using C# Visual Studio 2008 and the dataset query designer. The part of the query I am struggling with is:
WHERE (Land_use_code LIKE '%' +@Landusecode1 + '%')
However, when I run the query, it doesn't perform as I would expect. There are multiple table entries, with some as follows: R1 R3 R30 If I enter R and run the query, it returns nothing. If I enter R1, it returns all that match R1. If I enter R%, it will return R1 and R3, but not R30. If I enter R%%, I will get all records. I am wondering of a way to enable me to enter R% and get all records returned. Thanks, Joe
Joe Stansfield wrote:
If I enter R%%, I will get all records.
And if you enter R%%% do you get no records? Via SQL Server 2005 directly I am unable to replicate what you are seeing.
Joe Stansfield wrote:
I am using C# Visual Studio 2008 and the dataset query designer.
It is possible that the latter is the problem. In Server 2005 (not the designer) I get exactly or at least close to the behaviour you describe when I use the underscore rather than the percent sign. Especially when you try the different input values. Even if the SQL created excluded the percent signs from your where clause when you entered them manually it should still work. Instead with a single one you get only a single match. With two you get a two character match. That suggests the underline (single character match.) Presuming there isn't some other assumption that is wrong - such as that you are actually running some other query because this one didn't get applied. Simple test. Remove the percent entirely from the expression. Then test with input values: R, R_ and R%. Those should give different results. If not then I would suspect that there is some other assumption that is causing the problem.
-
Joe Stansfield wrote:
If I enter R%%, I will get all records.
And if you enter R%%% do you get no records? Via SQL Server 2005 directly I am unable to replicate what you are seeing.
Joe Stansfield wrote:
I am using C# Visual Studio 2008 and the dataset query designer.
It is possible that the latter is the problem. In Server 2005 (not the designer) I get exactly or at least close to the behaviour you describe when I use the underscore rather than the percent sign. Especially when you try the different input values. Even if the SQL created excluded the percent signs from your where clause when you entered them manually it should still work. Instead with a single one you get only a single match. With two you get a two character match. That suggests the underline (single character match.) Presuming there isn't some other assumption that is wrong - such as that you are actually running some other query because this one didn't get applied. Simple test. Remove the percent entirely from the expression. Then test with input values: R, R_ and R%. Those should give different results. If not then I would suspect that there is some other assumption that is causing the problem.
Sorry about the delayed reply - christmas / new year break over here in sunny Australia! I tried all of the things above, and get identical results when using R% as I do R_. I have found that when using R%% I do get all records, but I have since discovered this is only because the maximum length of a record is 3 characters. I get the same result if I use R%%%. However, when I use the wildcard part of the query on the Locality choice, it works as I would expect it too, and returns all results that start with, say, 'Hob', if I enter Hob%. The only difference I can think of between the two is that Locality is stored as 'NVARCHAR(50)' and Land_Use_Code as 'NCHAR(5)'. I would not have thought this would cause a problem, but I may be wrong! In the mean time I will work around it and add multiple %% signs to the parameter before the query is executed. Unfortunately I have tried to get my head around writing parametrized queries from scratch, but have not been able to find a good resource that explains it along with using it to then fill a DataGridView. Thanks for all your help though guys - has been great and I feel like I have learnt a lot!