Need some help with FullText Search

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 2005 Forums

Transact-SQL (2005)

Need some help with FullText Search

Author

Topic

hjavaher
Starting Member

16 Posts

Posted - 2009-06-29 : 12:50:08

Hi all,
I am quite a newbie to SQL in general. Although I can find my way around it I have just begun Fulltext Searching and as I am finding out it's a whole new beast to conquer. I am currently in a process of making a site that should search through the Title, Description and the Tag columns of media (Images and Videos) table a return the best possible results that match. The thing is that the end user may input many keywords in the field for example they may search for "Britney Spears Music Video Circus" The site it self is made using ASP.NET and I do have the ability of manipulating the query string before sending it to the server as a parameter. I currently have the following to accomplish this but it is now adequate as it does not search all possibilities.

Table structure:
id - int - identity - PK
OwnerID - int
MediaTitle - nvarchar(100)
MediaFileName - nvarchar(100)
MediaDesc - nvarchar(MAX)
MediaTags - nvarchar(MAX)
MediaVoteCount - int
MediaVoteSum - int
IsVideo - bit
IsImage - bit
DateAdded - datetime
DateModified - datetime
Allowed - bit
IsPublic - bit
viewCount - int
DurationSec - nvarchar(50)
Duration - nvarchar(50)

SELECT MediaList.id, UserProfile.UserName, MediaList.MediaTitle, MediaList.MediaFileName, MediaList.MediaDesc, MediaList.MediaTags, 
        MediaList.MediaVoteCount, MediaList.MediaVoteSum, MediaList.DateAdded, MediaList.Allowed, MediaList.DurationSec, MediaList.Duration, 
        MediaList.viewCount, ftt.RANK
FROM [C265491_Neda].[C265491_Neda1].[MediaList] INNER JOIN
        [C265491_Neda].[C265491_Neda1].[UserProfile] ON MediaList.OwnerID = UserProfile.id INNER JOIN
CONTAINSTABLE ([C265491_Neda].[C265491_Neda1].[MediaList], * , @SearchQuery) as ftt ON
        ftt.[KEY] = MediaList.id
WHERE MediaList.Allowed = 1 AND MediaList.IsPublic = 1
ORDER BY ftt.RANK DESC

The @SearchQuery is compiled on the server side using C#.NET and can take on any form :) Currently I have it compiling '"Keyword1" OR "Keyword2" OR ....' for as many words there are in the Search Field.

I really really appreciate any help.

Thank you greatly in advance,
Jason

hjavaher
Starting Member

16 Posts

Posted - 2009-06-30 : 00:51:10

Any Ideas guys? I really really appreciate it.

Thanks,
Jason

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources