March 15, 2012 at 12:54 pm
here iam having a select query store proc using like operator
create PROCEDURE [dbo].[CategoryName] @VALUES varchar(1000)
AS
SELECT @VALUES = '%' + RTRIM(@VALUES) + '%';
SELECT SB.SubCategoryName,SB.SubCategoryId
FROM tblAdSubCategory SB
WHERE SubCategoryName LIKE @VALUES;
for example if the user is typing three words in the @VALUES parameter
it has to seperate the 3 word bu space give
exec
[CategoryName] @VALUES='some the name '
it has to search some seperately and the seperately
and then name seperately
and provide result for all the three words in the same output
the @Values parameter is a list of words, i have to loop through the list so that your where clause ultimately looks like this:
where 1 = 2
or somefield like %word1%
or somefield like %word2%
etc
if it is possible means please help me
March 15, 2012 at 1:04 pm
Sivaganesh,
I am seeing a lot of posts from you ont he same request and the people who respond ask you to provide sample data, sample table ALWAYS. But you fail to do so every single time. Poeple here will definitely help you but only if you prepare the stage for them to work on.
[/intentional CAPS ON]
FOLLOW THE NETTIQUETTES PROPERLY AND POST TABLE SAMPLE, DATA SAMPLE , ANY INDEXES THAT EXISTS ON YOUR TABLE
FOLLOW THIS ARTICLE ON HOW TO DO THAT : http://www.sqlservercentral.com/articles/Best+Practices/61537/
[/intentional CAPS OFF]
March 15, 2012 at 1:11 pm
i've done what you are asking in another post...if you followed the netiquette, as ColdCoffee suggests, I probably would have posted the solution.
in the meantime, if you search the forums for "civil war memorabilia", you can find the original post and adapt it to your solution.
Lowell
March 15, 2012 at 1:13 pm
You may want to do some reading in Books Online, specifically regarding Full Test Indexing.
Without anything else from you, don't expect much else than this.
March 15, 2012 at 1:15 pm
Lowell (3/15/2012)
in the meantime, if you search the forums for "civil war memorabilia", you can find the original post and adapt it to your solution.
:hehe: :w00t:
March 15, 2012 at 1:49 pm
March 15, 2012 at 1:53 pm
k, I'm going to try this one last time...
CREATE PROCEDURE [CategoryName]
@VALUES varchar(1000)
AS
SET @VALUES = REPLACE(RTRIM(LTRIM(@VALUES)), ' ', '%' );
SELECT c.CategoryName
, CategoryId
, SB.SubCategoryName
, SB.SubCategoryId
FROM tblAdCategory c
JOIN tblAdSubCategory SB
ON SB.CategoryId =c.CategoryId
WHERE c.CategoryName LIKE '%' + @VALUES + '%'
OR SB.SubCategoryName LIKE '%' + @VALUES + '%';
There is no loop. I don't know why you keep saying loop. this will accomplish what you want to do. It does it by using the wildcard character (%) in the spaces so that it looks for each word separately.
DONE!
Jared
CE - Microsoft
March 15, 2012 at 1:56 pm
my version, which accepts a dynamic number of words, splitting them by the space between words:
CREATE PROCEDURE [dbo].[CategoryName] @SEARCHSTRING varchar(1000)
AS
DECLARE @COLUMNNAME VARCHAR(128),
@vbCrLf CHAR(2)
--SET @SEARCHSTRING='civil war memorabilia'
SET @COLUMNNAME = 'SB.SubCategoryName'
SET @vbCrLf = CHAR(13) + CHAR(10)
SET @SEARCHSTRING = 'WHERE CHARINDEX(''' + REPLACE(@SEARCHSTRING,' ',''',' + @COLUMNNAME + ') > 0 '
+ @vbCrLf
+ ' AND CHARINDEX(''')
+ ''','
+ @COLUMNNAME + ') > 0'
+ @vbCrLf
SET @SEARCHSTRING = 'SELECT SB.SubCategoryName,SB.SubCategoryId FROM tblAdSubCategory SB ' + @SEARCHSTRING
PRINT @SEARCHSTRING
EXEC(@SEARCHSTRING)
Lowell
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply