Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
HenryFulmer
Posting Yak Master
110 Posts |
Posted - 2013-06-29 : 12:39:23
|
I have a query into which the user can pass a parameter as a search criteria SELECT * FROm TABLE1 WHERE SearchCode LIKE '%' + @SearchCode + '%' The SearchCode column conatins various combinations of letters that are separated by ";". The users want to be able to just put in "*" and return everything in the SearchCode column.How do I allow them to send in the * character as a parameter? |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-29 : 14:51:56
|
do you mean this?SELECT * FROm TABLE1 WHERE (SearchCode LIKE '%' + @SearchCode + '%' OR @SearchCode='*') ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
HenryFulmer
Posting Yak Master
110 Posts |
Posted - 2013-06-30 : 13:04:18
|
I don't think that's what I need.The user wants to be able to put in an "*" and then get all records returned regardless of their value. |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-06-30 : 13:26:35
|
quote: Originally posted by HenryFulmer I don't think that's what I need.The user wants to be able to put in an "*" and then get all records returned regardless of their value.
[CODE]DECLARE @NewSearchCode VARCHAR(20);SELECT @NewSearchCode = (CASE WHEN @SearchCode= '*' THEN '%' ELSE '%' + @SearchCode + '%' END);SELECT * FROm TABLE1 WHERE SearchCode LIKE @NewSearchCode[/CODE] |
|
|
HenryFulmer
Posting Yak Master
110 Posts |
Posted - 2013-06-30 : 14:00:24
|
Thanks. I ended up using this.SELECT * FROM TABLE1 WHERE SearchCode LIKE '%' + CASE WHEN @SearchCode ='*' THEN '' ELSE @SearchCode END + '%' Thanks for the help. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-01 : 01:23:16
|
quote: Originally posted by HenryFulmer I don't think that's what I need.The user wants to be able to put in an "*" and then get all records returned regardless of their value.
thats exactly what my suggestion doesDid you try it at all?When you pass * @SearchCode='*' becomes trueso it will bypass (ignore) the filter SearchCode LIKE '%' + @SearchCode + '%' and you'll get all records from the table which was exactly what you were looking at------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-01 : 08:41:49
|
quote: Originally posted by visakh16
quote: Originally posted by HenryFulmer I don't think that's what I need.The user wants to be able to put in an "*" and then get all records returned regardless of their value.
thats exactly what my suggestion doesDid you try it at all?When you pass * @SearchCode='*' becomes trueso it will bypass (ignore) the filter SearchCode LIKE '%' + @SearchCode + '%' and you'll get all records from the table which was exactly what you were looking at------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
Don't want to beat a dead horse to death since the OP has marked it as resolved and has moved on. Nonetheless, the queries are not exactly the same. Mumu's and Henry's query will exclude any rows where SearchCode is null while yours will pick up those as well, and that exclusion perhaps was the behavior he was looking for.Or it just may be that logically it is easier for him to understand and maitain what he wrote. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-01 : 10:39:56
|
quote: Originally posted by James K
quote: Originally posted by visakh16
quote: Originally posted by HenryFulmer I don't think that's what I need.The user wants to be able to put in an "*" and then get all records returned regardless of their value.
thats exactly what my suggestion doesDid you try it at all?When you pass * @SearchCode='*' becomes trueso it will bypass (ignore) the filter SearchCode LIKE '%' + @SearchCode + '%' and you'll get all records from the table which was exactly what you were looking at------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
Don't want to beat a dead horse to death since the OP has marked it as resolved and has moved on. Nonetheless, the queries are not exactly the same. Mumu's and Henry's query will exclude any rows where SearchCode is null while yours will pick up those as well, and that exclusion perhaps was the behavior he was looking for.Or it just may be that logically it is easier for him to understand and maitain what he wrote.
Whats the problem in understanding whether I interpreted the requirement correctly or not? That was all I wanted to know.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|