Full-text Search

Hello, I have a full-text enabled field in my table has the following
content as an example:
"SQL Server is a comprehensive, integrated, end-to-end data solution
that empowers your people by providing a more secure, reliable, and
productive platform for enterprise data and BI applications. SQL Server
2005 delivers powerful, familiar tools to IT professionals as well as
to information workers, reducing the complexity of creating, deploying,
managing, and using enterprise data and analytical applications on
platforms ranging from mobile devices to enterprise data systems."
My question: Is there a way to return only the text that follows a
keyword specified as a parameter in the select statement? for example
the above content would be selected if i search for "reducing" but i
would like to return in that case:
"reducing the complexity of creating, deploying, managing, and using
enterprise data and analytical applications on platforms ranging from
mobile devices to enterprise data systems." as that piece of text
follows the word "reducing"
Any ideas would be appreciated.
Thanks,
Maya
There is no good way of handling this. You can use charindex to identify
word boundaries but this doesn't work well for FreeText type searches. I
posted a tsql solution in the other newsgroups you also posted to.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Maya" <kfoury@.gmail.com> wrote in message
news:1140309896.597776.60410@.o13g2000cwo.googlegro ups.com...
> Hello, I have a full-text enabled field in my table has the following
> content as an example:
> "SQL Server is a comprehensive, integrated, end-to-end data solution
> that empowers your people by providing a more secure, reliable, and
> productive platform for enterprise data and BI applications. SQL Server
> 2005 delivers powerful, familiar tools to IT professionals as well as
> to information workers, reducing the complexity of creating, deploying,
> managing, and using enterprise data and analytical applications on
> platforms ranging from mobile devices to enterprise data systems."
>
> My question: Is there a way to return only the text that follows a
> keyword specified as a parameter in the select statement? for example
> the above content would be selected if i search for "reducing" but i
> would like to return in that case:
>
> "reducing the complexity of creating, deploying, managing, and using
> enterprise data and analytical applications on platforms ranging from
> mobile devices to enterprise data systems." as that piece of text
> follows the word "reducing"
>
> Any ideas would be appreciated.
>
> Thanks,
>
> Maya
>
|||Hi Maya
We did a similar thing but with the added bonus of keyword
highlighting. In ASP you pass your text column plus an array of the
search words to the function and it returns a google-like keyword
highlighting with surrounding text.
Marc
Function search_HighlightKeywords(description, arrWords)
Dim Summary
Summary = ""
' Ensure spaces at start and end for our regex
description = " " & description & " "
Dim word
For Each word In arrWords
Dim RegEx
Set RegEx = New RegExp
RegEx.Global = True
RegEx.IgnoreCase = True
word = Replace(word, """", "")
RegEx.Pattern = "[\s\-].{1,30}(" & word & ")[^a-z].{1,30}[\s\-]"
Dim Matches
Set Matches = RegEx.Execute(description)
Dim Match
For Each Match In Matches
If Len(Summary) < 300 Then
Summary = Summary & "..." & search_HighlightTextWord(match,
word)
End If
Next
Next
' Ensure all words highlighted
For Each word In arrWords
Summary = search_HighlightTextWord(Summary, word)
Next
search_HighlightKeywords = Summary
End Function
Function search_HighlightTextWord(text, word)
Dim t1
t1 = InStr(1, text, word, 1)
If t1>1 Then
search_HighlightTextWord = Left(text, t1-1) & "<b>" & _
Mid(text, t1, Len(word)) & "</b>" & Mid(text, t1 + Len(word))
Else
search_HighlightTextWord = text
End If
End Function