November 1, 2012 at 9:03 am
Hello Gurus,
I am little perplexed with this search thing, hoping for your help.
here is what I have in my query/report search by text
declare @search varchar(max)
select @search='cake' --'%%'
create table #ITEM
(
item_desc varchar(50) )
INSERT INTO #ITEM ( Item_desc)
SELECT 'Cake 1'
UNION ALL
SELECT'Cake 2'
UNION ALL
SELECT 'Cake 3'
UNION ALL
SELECT 'Icecream cake'
UNION ALL
SELECT 'choclate cake'
UNION ALL
SELECT 'stuffed cake pastry'
UNION ALL
SELECT 'cake list'
UNION ALL
SELECT 'cake'
select * from #ITEM where item_desc like '%'+@search+ '%'
drop table #ITEM
now I have to enable following criteria,
--------------------------------------------------------------------
for instance If 'cake%' is entered then it should only bring anything that starts with cake
if user enters '%cake%' then it should bring everywhere 'cake' word is
if user enters '%cake' then it should bring when the word ends with 'cake'
if user enters '%%' it should bring everything.
if user enters 'cake' it should only bring 'cake' nothing else
% sign will be added by user in report depending on his preference for search.
thank you !!
November 1, 2012 at 9:09 am
What is the problem you are facing as the query is correct all you need to do is remove the % from the where clause if the user will supply them in the report.
November 1, 2012 at 9:11 am
select * from #ITEM where item_desc like + @search
Here is proof:
set @search='cake%'
select * from #ITEM where item_desc like + @search
set @search = '%cake'
select * from #ITEM where item_desc like + @search
set @search = '%cake%'
select * from #ITEM where item_desc like + @search
set @search = 'cake'
select * from #ITEM where item_desc like + @search
set @search = '%%'
select * from #ITEM where item_desc like + @search
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 1, 2012 at 9:15 am
thank you !!
i tried that but it did not work. can you please tell me what am I missing here?
declare @search varchar(max)
select @search='%cake'
create table #ITEM
(
item_desc varchar(50) )
INSERT INTO #ITEM ( Item_desc)
SELECT 'Cake 1'
UNION ALL
SELECT'Cake 2'
UNION ALL
SELECT 'Cake 3'
UNION ALL
SELECT 'Icecream cake'
UNION ALL
SELECT 'choclate cake'
UNION ALL
SELECT 'stuffed cake pastry'
UNION ALL
SELECT 'cake list'
select * from #ITEM where item_desc like '@search'
drop table #ITEM
November 1, 2012 at 9:17 am
thank you Sean, I will try that and will come back to you.
November 1, 2012 at 9:18 am
SQL_path (11/1/2012)
thank you !!i tried that but it did not work. can you please tell me what am I missing here?
declare @search varchar(max)
select @search='%cake'
create table #ITEM
(
item_desc varchar(50) )
INSERT INTO #ITEM ( Item_desc)
SELECT 'Cake 1'
UNION ALL
SELECT'Cake 2'
UNION ALL
SELECT 'Cake 3'
UNION ALL
SELECT 'Icecream cake'
UNION ALL
SELECT 'choclate cake'
UNION ALL
SELECT 'stuffed cake pastry'
UNION ALL
SELECT 'cake list'
select * from #ITEM where item_desc like '@search'
drop table #ITEM
The problem is that the parameter is actually a string, just remove the ' around the parameter like such and it will evaluate the contents of the parameter not the string '@search'
select * from #ITEM where item_desc like @search
November 1, 2012 at 9:34 am
I would use Anthony's code. He is correct that you are escaping out the parameter and making it a literal when you use quotes.
What you want from the code is (please don't use select *)
select item_desc from #ITEM where item_desc like 'cake%'
That's if the user entered "cake%" in their report box. If you have this code:
select item_desc from #ITEM where item_desc like '@search'
then you end up running this code
select item_desc from #ITEM where item_desc like '@search'
If you use Anthony's code, you end up with the first set of code being run.
November 1, 2012 at 9:49 am
Wow I have no idea why I put that plus in my code. :blush:
Definitely don't need it.
select * from #ITEM where item_desc like @search
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 1, 2012 at 10:15 am
thank you a tonn. this forum rocks !! 🙂
November 1, 2012 at 11:00 am
Gurus,
A quick q how do you enable search like this below . It returned nothing for me.
I was using set Replace(@search,'%','[%]') with my first code %+@search+% before.
now with changed requirements, how can i enable this below. thank you.
declare @search varchar(max)
select @search='50'
create table #ITEM
(
item_desc varchar(50) )
INSERT INTO #ITEM ( Item_desc)
SELECT 'Icecream cake'
UNION ALL
SELECT 'stuffed cake pastry'
UNION ALL
SELECT '50% fat'
select item_desc from #ITEM where item_desc like @search
drop table #ITEM
November 1, 2012 at 11:36 am
SQL_path (11/1/2012)
Gurus,A quick q how do you enable search like this below . It returned nothing for me.
I was using set Replace(@search,'%','[%]') with my first code %+@search+% before.
now with changed requirements, how can i enable this below. thank you.
declare @search varchar(max)
select @search='50'
create table #ITEM
(
item_desc varchar(50) )
INSERT INTO #ITEM ( Item_desc)
SELECT 'Icecream cake'
UNION ALL
SELECT 'stuffed cake pastry'
UNION ALL
SELECT '50% fat'
select item_desc from #ITEM where item_desc like @search
drop table #ITEM
I am a little confused about what you want it to return. The way you have this right now there is no wildcard because your @search has no '%'. When you use like and there is no wildcard it behaves like an equal. Given your sample data and a search value of "50" what should be returned?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 1, 2012 at 1:21 pm
Sean Lange (11/1/2012)
SQL_path (11/1/2012)
Gurus,A quick q how do you enable search like this below . It returned nothing for me.
I was using set Replace(@search,'%','[%]') with my first code %+@search+% before.
now with changed requirements, how can i enable this below. thank you.
declare @search varchar(max)
select @search='50'
create table #ITEM
(
item_desc varchar(50) )
INSERT INTO #ITEM ( Item_desc)
SELECT 'Icecream cake'
UNION ALL
SELECT 'stuffed cake pastry'
UNION ALL
SELECT '50% fat'
select item_desc from #ITEM where item_desc like @search
drop table #ITEM
I am a little confused about what you want it to return. The way you have this right now there is no wildcard because your @search has no '%'. When you use like and there is no wildcard it behaves like an equal. Given your sample data and a search value of "50" what should be returned?
Thank you Sean. I was trying to return '50% fat' . Yes I realized that it is acting like an equal, sorry for the confusion.
November 1, 2012 at 1:37 pm
Thank you Sean. I was trying to return '50% fat' . Yes I realized that it is acting like an equal, sorry for the confusion.
So you are all good now or did you still have an issue?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply