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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 [RESOLVED] wildcard * as parameter value

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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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.
Go to Top of Page

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]
Go to Top of Page

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.
Go to Top of Page

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 does
Did you try it at all?

When you pass * @SearchCode='*' becomes true
so 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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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 does
Did you try it at all?

When you pass * @SearchCode='*' becomes true
so 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 MVP
http://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.
Go to Top of Page

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 does
Did you try it at all?

When you pass * @SearchCode='*' becomes true
so 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 MVP
http://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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -