SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 [RESOLVED] wildcard * as parameter value
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

HenryFulmer
Posting Yak Master

USA
110 Posts

Posted - 06/29/2013 :  12:39:23  Show Profile  Reply with Quote
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?

Edited by - HenryFulmer on 06/30/2013 14:01:03

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/29/2013 :  14:51:56  Show Profile  Reply with Quote
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

USA
110 Posts

Posted - 06/30/2013 :  13:04:18  Show Profile  Reply with Quote
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

547 Posts

Posted - 06/30/2013 :  13:26:35  Show Profile  Reply with Quote
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.





DECLARE @NewSearchCode VARCHAR(20);

SELECT @NewSearchCode = (CASE WHEN @SearchCode= '*' THEN '%' ELSE '%' + @SearchCode + '%' END);

SELECT * FROm TABLE1 WHERE SearchCode LIKE  @NewSearchCode
Go to Top of Page

HenryFulmer
Posting Yak Master

USA
110 Posts

Posted - 06/30/2013 :  14:00:24  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 07/01/2013 :  01:23:16  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3715 Posts

Posted - 07/01/2013 :  08:41:49  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 07/01/2013 :  10:39:56  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000