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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-06 : 09:04:43
|
| Simon writes "I'm venturing into this area of SQL and trying to produce some good results from it but failing. Not because the search engine is bad but Im trying to do something perhaps that is not built in.I'm using Containstable which returns rank and Key, is there anything else that can identify the matched value.If I am searching a text field for a value of 'fishing' What I want is a field that is a precee of the resultegrank Key Precee134 60152 .....I like to go fishing.....Is this planned in the next FT index or is it an add in ortough you cant have that ?ThanksSimon/" |
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-05-06 : 15:46:54
|
| You simply join your containstable to your base table via the unique index used by FTSi.e.select [rank],title_id,title_name,author_name from titles join containstable(*,'"fishing"')on title_id=[key]order by [rank] desc |
 |
|
|
SimonTocker
Starting Member
1 Post |
Posted - 2002-05-07 : 07:19:03
|
| The Key word in my question was "Precee"The fields I am querying are large text fields sometime huge tomes are contained within them I only want to show the items in the field that it located. hence in my original post reference to ..... I like fishing...... The dots indicate other text not shown.Is there a way of locating the area of text where the location of the text was. Perhaps in a new Patch. Or is it there already as an undocumented feature where by you link to another tableperhaps its called, at a guess, ScanResults, which is keyed on the CONTAINSTABLE [KEY] field and has FieldName and position. It could be a 1 to many relationship detailing at each point where the text was found and perhaps a VC(50) field containing the words around that text.Isn't this what everyone wants in a search mechanism ? Otherwise to emulate it I need to return the Full text (however large) then try to replicate the same search mechanism in code to scan the text using VB/asp which lets face it just wont work, especially If I am using FREETEXTTABLE.Does anyone know of how this may be achieved ? or perhaps in a vague hope someone from MS who writes these things is reading this and might suggest it for a future release. |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-05-07 : 08:08:02
|
Sorry - my brain didn't seem to recognise precee as a word In our case we have the benefit of short_copy and long_copy fieldsshort_copy being varchar(4000) and long_copy being text - hence we just search the short_copy and and display it with obligatory click for more details link.In fact since our data comes from SAP and a copy library system we rebuild a search table each night and concatenate all the search fields like title,author,short_copy together into one fieldYour best bet is to post this to the MS sqlserver.fulltext newsgroup so that Hilary Cotter(FTS god) can have a look. I have just finished writing our sites search engine though and didn't see anything that could do what you want  CheersJasper |
|
|
|
|
|
|
|
|
Full text indexing