how to text search in MS Sql 2005

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)

how to text search in MS Sql 2005

Author

Topic

udaymahajan
Starting Member

17 Posts

Posted - 2008-10-04 : 06:15:32

Hi i want to search records using a word,or some text from database
for this
SELECT Frm.ForumID,
ForumCategoryName,
[ForumTopic]
,Post
FROM [Forums] Frm inner join
(SELECT ForumID,count(ForumID)as Post,max(ForumRepliesDate) as LastPost
FROM [ForumReplies] group by ForumID) Fr on
Frm.ForumID=Fr.ForumID inner join ForumReplies frs
on Fr.LastPost=frs.ForumRepliesDate inner join ForumCategories fc on
Frm.[ForumCategoryID]=fc.[ForumCategoryID]
where Frm.[Enable]=1 AND (FREETEXT(frs.ForumReplies,'Any higher level players'))
order by ForumDate desc

but using FREETEXT is shows error as

Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'ForumReplies' because it is not full-text indexed.

so how can i solve and display records

uday

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-04 : 06:38:28

you need to fulltext index the table or view before using full text search functions like FREETEXT,CONTAINS etc. However you could try like this

SELECT Frm.ForumID,
ForumCategoryName,
[ForumTopic]
,Post
FROM [Forums] Frm inner join
(SELECT ForumID,count(ForumID)as Post,max(ForumRepliesDate) as LastPost
FROM [ForumReplies] group by ForumID) Fr on
Frm.ForumID=Fr.ForumID inner join ForumReplies frs
on Fr.LastPost=frs.ForumRepliesDate inner join ForumCategories fc on
Frm.[ForumCategoryID]=fc.[ForumCategoryID]
where Frm.[Enable]=1 AND PATINDEX('%Any higher level players%',frs.ForumReplies)>0
order by ForumDate desc

udaymahajan
Starting Member

17 Posts

Posted - 2008-10-04 : 07:46:36

quote:
Originally posted by visakh16

you need to fulltext index the table or view before using full text search functions like FREETEXT,CONTAINS etc. However you could try like this

SELECT Frm.ForumID,
ForumCategoryName,
[ForumTopic]
,Post
FROM [Forums] Frm inner join
(SELECT ForumID,count(ForumID)as Post,max(ForumRepliesDate) as LastPost
FROM [ForumReplies] group by ForumID) Fr on
Frm.ForumID=Fr.ForumID inner join ForumReplies frs
on Fr.LastPost=frs.ForumRepliesDate inner join ForumCategories fc on
Frm.[ForumCategoryID]=fc.[ForumCategoryID]
where Frm.[Enable]=1 AND PATINDEX('%Any higher level players%',frs.ForumReplies)>0
order by ForumDate desc

this is not working i tried same solution,send another solution

uday

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-04 : 07:51:43

quote:
Originally posted by udaymahajan

quote:
Originally posted by visakh16

you need to fulltext index the table or view before using full text search functions like FREETEXT,CONTAINS etc. However you could try like this

SELECT Frm.ForumID,
ForumCategoryName,
[ForumTopic]
,Post
FROM [Forums] Frm inner join
(SELECT ForumID,count(ForumID)as Post,max(ForumRepliesDate) as LastPost
FROM [ForumReplies] group by ForumID) Fr on
Frm.ForumID=Fr.ForumID inner join ForumReplies frs
on Fr.LastPost=frs.ForumRepliesDate inner join ForumCategories fc on
Frm.[ForumCategoryID]=fc.[ForumCategoryID]
where Frm.[Enable]=1 AND PATINDEX('%Any higher level players%',frs.ForumReplies)>0
order by ForumDate desc

this is not working i tried same solution,send another solution

uday

before that post your exact requirement

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources