July 29, 2009 at 5:34 am
Hi,
I have a column (Named Description), in which some description e.g Automobiles is stored.
User enter multiple entries, to get the some search results.
e.g. 1) Speed AND Fuel Efficient OR Sports Utility
I need to add these in my where condition of my select query.
How do i do this?
select * from #table
where
(Description = 'Speed' AND Description = 'Fuel Efficient') or Description = 'Sports Utility';
-------------------------------------------------------------------------
For ur ease:
create table #table
(
Description varchar(max)
)
insert into #table (Description) values('Speed');
insert into #table (Description) values('jhg j jg jgkjhg jh vbv n Speed fsdfjh jbhjf');
insert into #table (Description) values('Fuel Efficient');
insert into #table (Description) values('dfnbmnb bmb ,mbdfdfhl kh kh Sports Utility dfsd fsdf');
insert into #table (Description) values('Sports Utility');
insert into #table (Description) values('dfgdf dfsf bnmbbtrerb mbmb');
insert into #table (Description) values('dfgdf dfsf bnmbbtrerb mbmb gdf dsfd fsdf df Speed gfdg fg dfg fdg Fuel Efficient gfg ffgf');
------------------------------------------------------------------------------------
select * from #table
where
(Description like '%Speed%' and '%Fuel Efficient%')
--this will throw error
This is not right, because the number of inputs is not fixed.
Help me.
Thanks.
July 29, 2009 at 6:06 am
1. make i/p parameter as string like:
@input = ',speed,fuelEfficient,sportsUtility'
Now Search like this:
SELECT * FROM #temp
WHERE
',' +@input + ','LIKE '%,' + Description + ',%'
July 29, 2009 at 6:41 am
How will this work?
Can u pls explain?
July 29, 2009 at 9:02 am
You can change you select query above to this:
select * from #table
where
([Description] like '%Speed%' AND [Description] like '%Fuel Efficient%')
OR ([description] LIKE '%Sports Utility%')
This will get rid of the error. The next part will be parsing the string that the user is entering. Are there fixed items the user can search for? Also, how many criteria are you going to let the user specify?
July 29, 2009 at 12:06 pm
Hi,
Thanks for the reply.
My main concern in this query is how to pass the multiple user inputs for the same column?
User might enter a big sentence with AND / OR conditions in the screen, there is no limit for that. :sick:
Repeating
Description like 'xyz' and Descrription like 'abc' etc etc is not a correct way, i guess.
I hope you got my question.
Thanks & Regards,
Sudhanva
July 29, 2009 at 12:28 pm
So without doing any SQL yet, let me make sure I got this correct.
1) You have an application that allows users to enter an indeterminate amout of characters into a text box.
2) The keywords separating the criteria would be: AND / OR
3) There is no criteria for what they can search for. They could search for "all your base" as easily as search for "fuel efficient"
So the first part will be to get the individual criteria. Parse the string and separate criteria based on the occurence of "AND" or "OR"
Then you need to figure out the logic per criteria. I will assume that the search will have at least 1 criteria and won't start with "AND". I would start with all of the ANDs and then do the ORs:
where (description like '%'+criteria1+'%' and description like '%'+isnull(criteria2,'')+'%' ...)
or description like '%'+isnull(criteriaX,'')+'%'
or description like '%'+isnull(criteriaX+1,'')+'%'
...
Now to start working on all of the logic to actually do it. I am not sure how much time I will have but I will work on it as soon as I can. If you come up with something first let me know.
July 30, 2009 at 12:22 am
Matt Wilhoite (7/29/2009)
3) There is no criteria for what they can search for. They could search for "all your base" as easily as search for "fuel efficient"So the first part will be to get the individual criteria. Parse the string and separate criteria based on the occurence of "AND" or "OR"
There is some criteria, refer the following.
Sl No. |E.g Keyword|Description
-----------------------------------------------------------------------------------------------------------
1 |Anesthesia|like Anesthesia, Anaesthesia, Anestesia
2|"Bipolar Disorder"|Only "Bipolar Disorder"
3|"Anesthesia"|Exact match
4|"Bipolar" "Disorder"|either "Bipolar" or "Disorder" only
5|Bipolar OR Disorder|same as Case 1 with a boolean
6 |Bipolar AND Disorder |same as Case 2 but irespective of order/sequence
7 |("Bipolar" OR "Disorder") |either ("Bipolar" or "Disorder") AND "Schizophrenia"
AND "Schizophrenia"
8|("Bipolar" AND "Disorder") |both (Bipolar AND Disorder) irespective of order. OR "Schizophrenia"
OR "Schizophrenia"
9 |("Bipolar Disorder") |both (Bipolar AND Disorder) same sequence OR "Schizophrenia"
OR "Schizophrenia"
I have done it like this:
For each criteria, i will parse the string, finally i should get the string as mentioned Column-2 above.
All i need to know, i guess is, how to put that (condition/criteria) in my where condition, of the query.
Help me.
Thanks & Regards,
Sudhanva
July 30, 2009 at 4:41 am
July 30, 2009 at 6:32 am
Good article. Going in the bookmarks. Thanks for that.
July 31, 2009 at 1:38 am
You are welcome
"Keep Trying"
July 31, 2009 at 3:10 am
That didnt help to find the solution for this post.
Help me.
July 31, 2009 at 4:12 am
I think you may be better looking at Fulltext indexing for this problem. This will improve performance of searches and give you built in thesauraus, inflectional and word splitting functionality. The following links provide more information.
Full text overview: http://msdn.microsoft.com/en-us/library/ms142547.aspx
How to set up full text index: http://msdn.microsoft.com/en-us/library/bb326035.aspx
Contains: http://msdn.microsoft.com/en-us/library/ms187787.aspx
This last item "Contains" provides much of the functionality you require for your searches (similar words, parenthisised ANDed and ORed search terms etc...) You would provide users with instructions on how to produce valid Contains search terms, or build a parser such as can be found at following link: http://www.sqlservercentral.com/articles/Full-Text+Search+(2008)/64248/
July 31, 2009 at 4:50 am
/*
I am not telling this is the best way but hope this can help u
*/
create table #table
(
Description varchar(max)
)
insert into #table (Description) values('Speed');
insert into #table (Description) values('jhg j jg jgkjhg jh vbv n Speed fsdfjh jbhjf');
insert into #table (Description) values('Fuel Efficient');
insert into #table (Description) values('dfnbmnb bmb ,mbdfdfhl kh kh Sports Utility dfsd fsdf');
insert into #table (Description) values('Sports Utility');
insert into #table (Description) values('dfgdf dfsf bnmbbtrerb mbmb');
insert into #table (Description) values('dfgdf dfsf bnmbbtrerb mbmb gdf dsfd fsdf df Speed gfdg fg dfg fdg Fuel Efficient gfg ffgf');
------------------------------------------------------------------------------------
create table #tableTest
(
Description varchar(max)
)
DECLARE @input VARCHAR(200)
/*
concatinate all i/p parametes as string with commma delimeter.
*/
SET @input = 'Speed,Fuel Efficient,Sports Utility,'
WHILE CHARINDEX(',',@input)0
BEGIN
INSERT INTO #tableTest
SELECT * FROM #table
WHERE description LIKE '%'+SUBSTRING(@input,1,CHARINDEX(',',@input)-1) +'%'
SET @input = REPLACE(@input,SUBSTRING(@input,1,CHARINDEX(',',@input)),'')
END
SELECT DISTINCT * FROM #tableTest
July 31, 2009 at 9:27 am
check out this one also.
http://sqlservercoollinks.blogspot.com/2009/03/using-case-in-where-clause-utilizing.html
August 5, 2009 at 3:15 am
That didnt help to find the solution for this post.
Help me.
:sick:
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply