Implementing a search - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Implementing a search

Author

Topic

Apples
Posting Yak Master

146 Posts

Posted - 2008-03-28 : 15:20:58

Here's my table:

----------------------------
News
----------------------------
ID | Headline | Article
----------------------------

I want the user to be able to search for keywords in articles. Here's my stored procedure:

CREATE PROCEDURE sproc_GetNewsSearch
(
@keyword varchar(50)
)
AS
SELECT Headline, Article
FROM News
WHERE Article LIKE '%' + @keyword + '%'

This will work fine if I search with one word. If I enter "zebra", it will find the articles containing "zebra".

The problem is when I search for more than one word. If I enter "monkey zebra", it won't search for articles containing "monkey" and "zebra", it will look for "monkey zebra".

How can I fix that?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-28 : 15:42:45

Try:-
CREATE PROCEDURE sproc_GetNewsSearch
(
@keyword varchar(50)
)
AS
SELECT Headline, Article
FROM News
WHERE Article LIKE '%' + REPLACE(@keyword,' ','%') + '%'

Apples
Posting Yak Master

146 Posts

Posted - 2008-03-28 : 15:59:33

Thanks visakh, that worked.

I have another question, I don't know if this is possible...

The solution you provided will only work if the words are placed in the exact order. For example:

If the article contains, "My name is visakh", and I search for "name visakh", the article will be returned.
But if the article contains, "visakh is my name", and I search for "name visakh", the article will not be returned.

Is there a way around this?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-02 : 06:03:07

[code]Declare @keyword varchar(50)
set @keyword='name visakh'
set @keyword=replace(@keyword,' ','.')

select data from
(
select 'My name is visakh' as data union all
select 'visakh is my name'
) as t
where
(
data like '%' + parsename(@keyword,2) + '%' + parsename(@keyword,1) + '%'
or
data like '%' + parsename(@keyword,1) + '%' + parsename(@keyword,2) + '%'
)
[/code]

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-02 : 06:12:18

Please note that you can search for maximum of 4 words only using PARSENAME

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-02 : 06:21:39

quote:
Originally posted by visakh16

Please note that you can search for maximum of 4 words only using PARSENAME

Yes it is

Madhivanan

Failing to plan is Planning to fail

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-04-02 : 21:39:24

I would be looking at the full text indexing features of SQL Server. What you are doing here will never scale properly or be partiularly good with approximations. Check BOL for details.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources