keyword 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

SQL Server 2000 Forums

Transact-SQL (2000)

keyword search

Author

Topic

ts_abbott@hotmail.com
Starting Member

36 Posts

Posted - 2005-03-02 : 06:46:43

Hi,

I need to build a stored procedure that takes 2 params - keywords and content and returns true/false.

The way I would like to it to work is to split the keywords into an array of words (split by a space) and return true if each word in the keyword array exists in the content param.

That I can specify a join condition similar to the following...

(AND a.Keywords IS NULL OR dbo.Search(a.Keywords, c.Content))

I have no idea how to go about this and googling hasn't proved to be of any help.

Does anyone know if this is possible?

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2005-03-02 : 09:00:08

Give us an example of what you mean

You can't teach an old mouse new clicks.

ts_abbott@hotmail.com
Starting Member

36 Posts

Posted - 2005-03-02 : 09:16:19

There are 2 main tables used:

1) Cars - stores vehicles for sale
2) Alerts - store details of vehicles that users are looking or

The aim is to match cars to alerts.

If a user is searching for a "1.8 GTI" (stored in Alerts.Keywords field) i need the function to search to make sure the words "1.8" and "GTI" both appear (in any order) in the Cars.Description field which may look something like "1.8 3dr Petrol GTI".

ts_abbott@hotmail.com
Starting Member

36 Posts

Posted - 2005-03-02 : 09:19:34

My final query make look something like...

SELECT c.*
FROM Cars c
INNER JOIN Alerts a ON a.Make=c.Make AND a.Model=c.Model
WHERE (@Colour IS NULL OR a.Colour=c.Colour)
AND (@MaxMileage IS NULL OR a.MaxMileage > c.Mileage)
AND (dbo.Search(a.Keywords, c.Keywords)) /* return true if there is a match */

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2005-03-03 : 12:33:56

Try the following bit of code.
If someone can come up with an easier method please post it

You can pass it your parameters like Keyword, Mileage and colour - I didn't add all that in but I think this might get you going.


Declare @KeyWords Varchar(255),
	@Pos int	,
	@Key1 varchar(10),
	@Key2 varchar(10),
	@Key3 varchar(10)

Set @KeyWords = '1.8 Golf GTI Petrol         '
Set @KeyWords = Rtrim(@Keywords)
--Print @Keywords

If (Select Patindex('% %', @Keywords) ) > 0
	Begin
		Set @Pos = (Patindex('% %',@KeyWords) - 1)
		Set @Key1 = Substring(@Keywords, 1, @Pos)
		Set @Keywords = Right(@Keywords, Len(@Keywords) - (@Pos + 1))
--		Print @Keywords

			If (Select Patindex('% %', @Keywords) ) > 0
				Begin
					Set @Pos = (Patindex('% %',@KeyWords) - 1)
					Set @Key2 = Substring(@Keywords, 1, @Pos)
					Set @Keywords = Right(@Keywords, Len(@Keywords) - (@Pos + 1))
--					Print @Keywords

						If (Select Patindex('% %', @Keywords) ) > 0
							Begin
								Set @Pos = (Patindex('% %',@KeyWords) - 1)
								Set @Key3 = Substring(@Keywords, 1, @Pos)
								Set @Keywords = Right(@Keywords, Len(@Keywords) - (@Pos + 1))
--								Print @Keywords
							end
				end
	end

Print @Key1
Print @Key2
Print @Key3


Select *
from Cars 
where Keywords like '%' + @Key1 + '%'
and keywords like '%' + @Key2 + '%'
and Keywords like '%' + @Key3+ '%'

You can't teach an old mouse new clicks.

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-03-03 : 13:13:39

Why dont you use full-text indexing then you could do something like this


DECLARE @Keywords varchar(50)
SET @KeyWords = '1.8 GTI'
SET @KeyWords = REPLACE(@KeyWords,' ',' AND ')

SELECT *
FROM YourTable
WHERE CONTAINS(YourColumn, @KeyWords)

Andy

Edit:
You will have to handle extra spaces in the string to avoid any errors

Beauty is in the eyes of the beerholder

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2005-03-03 : 13:40:38

Yep, Full-text indexing would probably be an easier way of doing it.

You can't teach an old mouse new clicks.

ts_abbott@hotmail.com
Starting Member

36 Posts

Posted - 2005-03-07 : 16:20:55

excellent stuff! this is exactly what i needed!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources