how to use like operator for this store proc or any other method?

Question

Post reply

how to use like operator for this store proc or any other method?

Sivaganesh Tamilvendhan

Hall of Fame

Points: 3498
More actions
March 14, 2012 at 2:36 pm

#257948

here i just made one search store procedure
create PROCEDURE [CategoryName]
@VALUES varchar(1000)
AS
SELECT @VALUES = RTRIM(@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 ;
it is providing out put for exam ple if iam executing this store procedure
exec
[CategoryName] @VALUES='some'
it is providing search for only one sentence and proving result for that
but i need to search more word as like google search
for example
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
if it is possible means please help me

Viewing 15 posts - 1 through 15 (of 34 total)

You must be logged in to reply to this topic. Login to reply

Jared SSC Guru Points: 61793 More actions · Answer 1

CREATE PROCEDURE [CategoryName]

@VALUES varchar(1000)

AS

SET @VALUES = 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 + '%';

Try this.

EDIT: Changed SELECT to ST for trimming the variable

Jared
CE - Microsoft

Sivaganesh Tamilvendhan Hall of Fame Points: 3498 More actions · Answer 2

i execute ur store procedure like this

EXEC [dbo].[CategoryName]

@VALUES = N'the sep'

in execution i used two words the and sep

i know there wound be any values for the but i know there willbe value in subcatecory for sep

but i found null as result i think i should use some function

Jared SSC Guru Points: 61793 More actions · Answer 3

You can use replace function to replace spaces with %:

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 + '%';

Jared
CE - Microsoft

Sivaganesh Tamilvendhan Hall of Fame Points: 3498 More actions · Answer 4

Sivaganesh Tamilvendhan

Hall of Fame

Points: 3498

March 15, 2012 at 1:26 pm

#1460119

how it will work please tell me

Lowell SSC Guru Points: 323518 More actions · Answer 5

duplicate post.

no need to cross post to multiple forums it fractures the answers you get and makes posters duplicate others work.

the "Recent Posts" link shows us everything.

continue the thread here:

http://www.sqlservercentral.com/Forums/Topic1267763-391-1.aspx

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Lowell (3/15/2012)
duplicate post.
no need to cross post to multiple forums it fractures the answers you get and makes posters duplicate others work.
the "Recent Posts" link shows us everything.
continue the thread here:
http://www.sqlservercentral.com/Forums/Topic1267763-391-1.aspx

Lowell, check your link, I think it's broke.

Jared SSC Guru Points: 61793 More actions · Answer 7

Lowell (3/15/2012)
duplicate post.
no need to cross post to multiple forums it fractures the answers you get and makes posters duplicate others work.
the "Recent Posts" link shows us everything.
continue the thread here:

Links to same topic.

Jared
CE - Microsoft

Lowell SSC Guru Points: 323518 More actions · Answer 8

sorry gang, link is fixed.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

SQLKnowItAll (3/15/2012)
Lowell (3/15/2012)
duplicate post.
no need to cross post to multiple forums it fractures the answers you get and makes posters duplicate others work.
the "Recent Posts" link shows us everything.
continue the thread here:
Links to same topic.

Same topic different thread. OP thinks he may get a different answer on a different thread.

Lowell SSC Guru Points: 323518 More actions · Answer 10

Lynn Pettis (3/15/2012)
SQLKnowItAll (3/15/2012)
Lowell (3/15/2012)
duplicate post.
no need to cross post to multiple forums it fractures the answers you get and makes posters duplicate others work.
the "Recent Posts" link shows us everything.
continue the thread here:
Links to same topic.
Same topic different thread. OP thinks he may get a different answer on a different thread.

yeah if you look at his posts, he has more than a half a dozen on the same issue, but never follows up with sample data or other requests...just reposts ato a new topic.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jared SSC Guru Points: 61793 More actions · Answer 11

Lynn Pettis (3/15/2012)
SQLKnowItAll (3/15/2012)
Lowell (3/15/2012)
duplicate post.
no need to cross post to multiple forums it fractures the answers you get and makes posters duplicate others work.
the "Recent Posts" link shows us everything.
continue the thread here:
Links to same topic.
Same topic different thread. OP thinks he may get a different answer on a different thread.

Ah, yes. I said topic because here on SSC they call each thread a topic, as in "Add Topic" button. Thought it may be an English reference or something 😀

Jared
CE - Microsoft

Sivaganesh Tamilvendhan Hall of Fame Points: 3498 More actions · Answer 12

iam not getting proper loop structure thats why i posted two three post

and sorry for your inconvience

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

er.sivaganesh (3/15/2012)
iam not getting proper loop structure thats why i posted two three post
and sorry for your inconvience

First, you probably don't need a loop structure. As the number of words in the string grows the query will slow down.

Second, if you would help us help you by following the instructions in the first article I reference below in my signature block and give us what we keep asking you to provide you would get much better answers to your questions.

Please remember that we are volunteers here. No one pays us to help, we doing it because we want to when we can. To do that, you have to help us help you.

Sivaganesh Tamilvendhan Hall of Fame Points: 3498 More actions · Answer 14

sorry one question but we can have loop it also possible or not in sql server