We had a peculiar situation today where our lovely indexed view
obviously had textual data in it but some recently added rows were not
being returned in a CONTAINS query when they should have been.
Deleting and recreating the FT index fixed things immediately and I
can't seem to reproduce it but it worries me for our live deployment
(due imminently!).
Has anyone else seen this? Is it reproducible (and fixable?!)
Mark
Hi Mark,
Do you have a timestamp column in underlaying table(s) ?
Have you activated automatic change tracking for your index ?
Jean-Pierre Riehl
http://blog.djeepy1.net
http://www.bewise.fr
<mbedser@.gmail.com> wrote in message
news:1185555299.154393.224920@.d55g2000hsg.googlegr oups.com...
> We had a peculiar situation today where our lovely indexed view
> obviously had textual data in it but some recently added rows were not
> being returned in a CONTAINS query when they should have been.
> Deleting and recreating the FT index fixed things immediately and I
> can't seem to reproduce it but it worries me for our live deployment
> (due imminently!).
> Has anyone else seen this? Is it reproducible (and fixable?!)
> Mark
>
|||Change tracking does not require a timestamp column.
I think your problem will be solved if you do use change tracking (as the
previous poster points out).
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
"Jean-Pierre Riehl" <jean-pierre.riehl@.b-e-w-i-s-e.fr> wrote in message
news:1C0C02A3-0A2F-4294-97CA-59E453DE6780@.microsoft.com...
> Hi Mark,
> Do you have a timestamp column in underlaying table(s) ?
> Have you activated automatic change tracking for your index ?
> --
> Jean-Pierre Riehl
> http://blog.djeepy1.net
> http://www.bewise.fr
> <mbedser@.gmail.com> wrote in message
> news:1185555299.154393.224920@.d55g2000hsg.googlegr oups.com...
>
|||I apologize for my mistake. Incremental update requires a timestamp column
whereas change tracking doesn't.
Jean-Pierre Riehl
http://blog.djeepy1.net
http://www.bewise.fr
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23CQlfIT0HHA.1188@.TK2MSFTNGP04.phx.gbl...
> Change tracking does not require a timestamp column.
> I think your problem will be solved if you do use change tracking (as the
> previous poster points out).
> --
> 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
> "Jean-Pierre Riehl" <jean-pierre.riehl@.b-e-w-i-s-e.fr> wrote in message
> news:1C0C02A3-0A2F-4294-97CA-59E453DE6780@.microsoft.com...
>
2012年3月11日星期日
Full text index not populating correctly
Hi
I have a table with 4 columns which are full-text indexed. I have noticed
that certain words are not being indexed and therefore not returned in
searches.
I have also noticed that the position on the word in the column effects
whether it is indexed.
For example:
I need to search a column called "Subtitle" which contains information on
product model numbers. If i search for "KF18W420GB" on a column that
contains "KF18W420GB stainless steel" then nothing is returned. If i change
the value in the database to "KF18W420GB" and repopulate the index then the
search works. Similarly, if i change the value in the database to "another
word KF18W420GB stainless steel" the search also works.
Does anyone know why this is? I am using SQL Server 7 on NT 4 - i am
guessing this is probably the problem and i am attempting to upgrade.
Thanks in advance.
Dan
Dan,
It would depend upon the OS platform wordbreaker, in this case the NT4.0
infosoft.dll. It also depends upon the exact SQL Server 7.0 FTS query
(contains vs. freetext) that you are using. Could you post the exact
CONTAINS* or FREETEXT* FTS query you are using as well as a sample of the
exact text you are expecting to return.
FYI, as you're using NT4.0, you can easily move in the Win2K version of
infosoft.dll as a test. If you want to do this, I have the exact methods on
how to do this.
Regards,
John
"Dan" <dan@.nospamplan9.co.uk> wrote in message
news:O4fDi9amEHA.3372@.TK2MSFTNGP15.phx.gbl...
> Hi
> I have a table with 4 columns which are full-text indexed. I have noticed
> that certain words are not being indexed and therefore not returned in
> searches.
> I have also noticed that the position on the word in the column effects
> whether it is indexed.
> For example:
> I need to search a column called "Subtitle" which contains information on
> product model numbers. If i search for "KF18W420GB" on a column that
> contains "KF18W420GB stainless steel" then nothing is returned. If i
change
> the value in the database to "KF18W420GB" and repopulate the index then
the
> search works. Similarly, if i change the value in the database to "another
> word KF18W420GB stainless steel" the search also works.
> Does anyone know why this is? I am using SQL Server 7 on NT 4 - i am
> guessing this is probably the problem and i am attempting to upgrade.
> Thanks in advance.
> Dan
>
|||Hi
i have tried using CONTAINSTABLE also but the result is the same as the
query below:
SELECT
KEY_TBL.Rank,
FT_TBL.LinkText,
FT_TBL.PageId,
FT_TBL.template
FROM (tblPageContent AS FT_TBL
INNER JOIN FREETEXTTABLE(tblPageContent,*,'KF18W420GB') AS KEY_TBL
ON FT_TBL.PageID = KEY_TBL.[KEY])
WHERE FT_TBL.Search !=0 ORDER BY KEY_TBL.RANK DESC
Select @.@.version:
Microsoft SQL Server 7.00 - 7.00.1063 (Intel X86) Apr 9 2002 14:18:16
Copyright (c) 1988-2002 Microsoft Corporation Standard Edition on Windows
NT 4.0 (Build 1381: Service Pack 6)
Select @.@.language:
us_english
"John Kane" <jt-kane@.comcast.net> wrote in message
news:e3EU%231bmEHA.3356@.TK2MSFTNGP14.phx.gbl...
> Dan,
> It would depend upon the OS platform wordbreaker, in this case the NT4.0
> infosoft.dll. It also depends upon the exact SQL Server 7.0 FTS query
> (contains vs. freetext) that you are using. Could you post the exact
> CONTAINS* or FREETEXT* FTS query you are using as well as a sample of the
> exact text you are expecting to return.
> FYI, as you're using NT4.0, you can easily move in the Win2K version of
> infosoft.dll as a test. If you want to do this, I have the exact methods
> on
> how to do this.
> Regards,
> John
>
> "Dan" <dan@.nospamplan9.co.uk> wrote in message
> news:O4fDi9amEHA.3372@.TK2MSFTNGP15.phx.gbl...
> change
> the
>
|||Dan,
If you re-write the query and remove the where clause (WHERE FT_TBL.Search
!=0)
SELECT KEY_TBL.Rank, FT_TBL.LinkText, FT_TBL.PageId, FT_TBL.template
FROM tblPageContent AS FT_TBL
INNER JOIN FREETEXTTABLE(tblPageContent,*,'KF18W420GB') AS KEY_TBL
ON FT_TBL.PageID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
does it return any &/or expectant results? If not, then as this is SQL 7.0
on NT4.0, this may be a NT4.0 wordbreaking issue, and unfortunately, I don't
have an NT4.0 installation to test the NT4.0 version of infosoft.dll. In the
row that contains the above search string 'KF18W420GB' is there any other
characters &/or punctuation that is in contact or touching the search
string?
Thanks,
John
"Dan" <dan@.nospamplan9.co.uk> wrote in message
news:eqzVCZemEHA.2616@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Hi
> i have tried using CONTAINSTABLE also but the result is the same as the
> query below:
> SELECT
> KEY_TBL.Rank,
> FT_TBL.LinkText,
> FT_TBL.PageId,
> FT_TBL.template
> FROM (tblPageContent AS FT_TBL
> INNER JOIN FREETEXTTABLE(tblPageContent,*,'KF18W420GB') AS KEY_TBL
> ON FT_TBL.PageID = KEY_TBL.[KEY])
> WHERE FT_TBL.Search !=0 ORDER BY KEY_TBL.RANK DESC
> Select @.@.version:
> Microsoft SQL Server 7.00 - 7.00.1063 (Intel X86) Apr 9 2002 14:18:16
> Copyright (c) 1988-2002 Microsoft Corporation Standard Edition on Windows
> NT 4.0 (Build 1381: Service Pack 6)
> Select @.@.language:
> us_english
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:e3EU%231bmEHA.3356@.TK2MSFTNGP14.phx.gbl...
the[vbcol=seagreen]
noticed[vbcol=seagreen]
on
>
|||No, that doesn't make a difference. There is no other words/characters other
than those specified.
I will endevour to upgrade the server.
Thanks
Dan
"John Kane" <jt-kane@.comcast.net> wrote in message
news:uIexBlgmEHA.512@.TK2MSFTNGP10.phx.gbl...
> Dan,
> If you re-write the query and remove the where clause (WHERE FT_TBL.Search
> !=0)
> SELECT KEY_TBL.Rank, FT_TBL.LinkText, FT_TBL.PageId, FT_TBL.template
> FROM tblPageContent AS FT_TBL
> INNER JOIN FREETEXTTABLE(tblPageContent,*,'KF18W420GB') AS KEY_TBL
> ON FT_TBL.PageID = KEY_TBL.[KEY]
> ORDER BY KEY_TBL.RANK DESC
> does it return any &/or expectant results? If not, then as this is SQL 7.0
> on NT4.0, this may be a NT4.0 wordbreaking issue, and unfortunately, I
> don't
> have an NT4.0 installation to test the NT4.0 version of infosoft.dll. In
> the
> row that contains the above search string 'KF18W420GB' is there any other
> characters &/or punctuation that is in contact or touching the search
> string?
> Thanks,
> John
>
>
> "Dan" <dan@.nospamplan9.co.uk> wrote in message
> news:eqzVCZemEHA.2616@.tk2msftngp13.phx.gbl...
> the
> noticed
> on
>
I have a table with 4 columns which are full-text indexed. I have noticed
that certain words are not being indexed and therefore not returned in
searches.
I have also noticed that the position on the word in the column effects
whether it is indexed.
For example:
I need to search a column called "Subtitle" which contains information on
product model numbers. If i search for "KF18W420GB" on a column that
contains "KF18W420GB stainless steel" then nothing is returned. If i change
the value in the database to "KF18W420GB" and repopulate the index then the
search works. Similarly, if i change the value in the database to "another
word KF18W420GB stainless steel" the search also works.
Does anyone know why this is? I am using SQL Server 7 on NT 4 - i am
guessing this is probably the problem and i am attempting to upgrade.
Thanks in advance.
Dan
Dan,
It would depend upon the OS platform wordbreaker, in this case the NT4.0
infosoft.dll. It also depends upon the exact SQL Server 7.0 FTS query
(contains vs. freetext) that you are using. Could you post the exact
CONTAINS* or FREETEXT* FTS query you are using as well as a sample of the
exact text you are expecting to return.
FYI, as you're using NT4.0, you can easily move in the Win2K version of
infosoft.dll as a test. If you want to do this, I have the exact methods on
how to do this.
Regards,
John
"Dan" <dan@.nospamplan9.co.uk> wrote in message
news:O4fDi9amEHA.3372@.TK2MSFTNGP15.phx.gbl...
> Hi
> I have a table with 4 columns which are full-text indexed. I have noticed
> that certain words are not being indexed and therefore not returned in
> searches.
> I have also noticed that the position on the word in the column effects
> whether it is indexed.
> For example:
> I need to search a column called "Subtitle" which contains information on
> product model numbers. If i search for "KF18W420GB" on a column that
> contains "KF18W420GB stainless steel" then nothing is returned. If i
change
> the value in the database to "KF18W420GB" and repopulate the index then
the
> search works. Similarly, if i change the value in the database to "another
> word KF18W420GB stainless steel" the search also works.
> Does anyone know why this is? I am using SQL Server 7 on NT 4 - i am
> guessing this is probably the problem and i am attempting to upgrade.
> Thanks in advance.
> Dan
>
|||Hi
i have tried using CONTAINSTABLE also but the result is the same as the
query below:
SELECT
KEY_TBL.Rank,
FT_TBL.LinkText,
FT_TBL.PageId,
FT_TBL.template
FROM (tblPageContent AS FT_TBL
INNER JOIN FREETEXTTABLE(tblPageContent,*,'KF18W420GB') AS KEY_TBL
ON FT_TBL.PageID = KEY_TBL.[KEY])
WHERE FT_TBL.Search !=0 ORDER BY KEY_TBL.RANK DESC
Select @.@.version:
Microsoft SQL Server 7.00 - 7.00.1063 (Intel X86) Apr 9 2002 14:18:16
Copyright (c) 1988-2002 Microsoft Corporation Standard Edition on Windows
NT 4.0 (Build 1381: Service Pack 6)
Select @.@.language:
us_english
"John Kane" <jt-kane@.comcast.net> wrote in message
news:e3EU%231bmEHA.3356@.TK2MSFTNGP14.phx.gbl...
> Dan,
> It would depend upon the OS platform wordbreaker, in this case the NT4.0
> infosoft.dll. It also depends upon the exact SQL Server 7.0 FTS query
> (contains vs. freetext) that you are using. Could you post the exact
> CONTAINS* or FREETEXT* FTS query you are using as well as a sample of the
> exact text you are expecting to return.
> FYI, as you're using NT4.0, you can easily move in the Win2K version of
> infosoft.dll as a test. If you want to do this, I have the exact methods
> on
> how to do this.
> Regards,
> John
>
> "Dan" <dan@.nospamplan9.co.uk> wrote in message
> news:O4fDi9amEHA.3372@.TK2MSFTNGP15.phx.gbl...
> change
> the
>
|||Dan,
If you re-write the query and remove the where clause (WHERE FT_TBL.Search
!=0)
SELECT KEY_TBL.Rank, FT_TBL.LinkText, FT_TBL.PageId, FT_TBL.template
FROM tblPageContent AS FT_TBL
INNER JOIN FREETEXTTABLE(tblPageContent,*,'KF18W420GB') AS KEY_TBL
ON FT_TBL.PageID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
does it return any &/or expectant results? If not, then as this is SQL 7.0
on NT4.0, this may be a NT4.0 wordbreaking issue, and unfortunately, I don't
have an NT4.0 installation to test the NT4.0 version of infosoft.dll. In the
row that contains the above search string 'KF18W420GB' is there any other
characters &/or punctuation that is in contact or touching the search
string?
Thanks,
John
"Dan" <dan@.nospamplan9.co.uk> wrote in message
news:eqzVCZemEHA.2616@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Hi
> i have tried using CONTAINSTABLE also but the result is the same as the
> query below:
> SELECT
> KEY_TBL.Rank,
> FT_TBL.LinkText,
> FT_TBL.PageId,
> FT_TBL.template
> FROM (tblPageContent AS FT_TBL
> INNER JOIN FREETEXTTABLE(tblPageContent,*,'KF18W420GB') AS KEY_TBL
> ON FT_TBL.PageID = KEY_TBL.[KEY])
> WHERE FT_TBL.Search !=0 ORDER BY KEY_TBL.RANK DESC
> Select @.@.version:
> Microsoft SQL Server 7.00 - 7.00.1063 (Intel X86) Apr 9 2002 14:18:16
> Copyright (c) 1988-2002 Microsoft Corporation Standard Edition on Windows
> NT 4.0 (Build 1381: Service Pack 6)
> Select @.@.language:
> us_english
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:e3EU%231bmEHA.3356@.TK2MSFTNGP14.phx.gbl...
the[vbcol=seagreen]
noticed[vbcol=seagreen]
on
>
|||No, that doesn't make a difference. There is no other words/characters other
than those specified.
I will endevour to upgrade the server.
Thanks
Dan
"John Kane" <jt-kane@.comcast.net> wrote in message
news:uIexBlgmEHA.512@.TK2MSFTNGP10.phx.gbl...
> Dan,
> If you re-write the query and remove the where clause (WHERE FT_TBL.Search
> !=0)
> SELECT KEY_TBL.Rank, FT_TBL.LinkText, FT_TBL.PageId, FT_TBL.template
> FROM tblPageContent AS FT_TBL
> INNER JOIN FREETEXTTABLE(tblPageContent,*,'KF18W420GB') AS KEY_TBL
> ON FT_TBL.PageID = KEY_TBL.[KEY]
> ORDER BY KEY_TBL.RANK DESC
> does it return any &/or expectant results? If not, then as this is SQL 7.0
> on NT4.0, this may be a NT4.0 wordbreaking issue, and unfortunately, I
> don't
> have an NT4.0 installation to test the NT4.0 version of infosoft.dll. In
> the
> row that contains the above search string 'KF18W420GB' is there any other
> characters &/or punctuation that is in contact or touching the search
> string?
> Thanks,
> John
>
>
> "Dan" <dan@.nospamplan9.co.uk> wrote in message
> news:eqzVCZemEHA.2616@.tk2msftngp13.phx.gbl...
> the
> noticed
> on
>
2012年2月19日星期日
FTS results page question
I'm an FTS newb so please be gentle.
When using most search services the results returned are accompanied by a
snippet or excerpt of the full text that scored a hit showing the matched
word(s)/phrase in context.
For example, if I search for "full-text search" I might receive this as one
entry in the list of results:
... immediately alerts you if a server gets out ... a graphical
administration interface, an SQL query tool ... It provides the full-text
search based on Microsoft Indexing ...
Can I get a similar snippet or excerpt using FTS or will I have to roll my
own solution? Any suggestions regarding how to implement the latter would
be greatly appreciated.
Thanks,
Will
Hi Will,
Not to worry, as I and others who post the replies here, know a great deal
about SQL Full-text Search (FTS ;-)
Could you post the output of the following SQL script that will provide info
on your SQL Server and OS platform?
use master
go
SELECT @.@.language
SELECT @.@.version
go
If I correctly understand your requirement, you want a range of words, plus
and minus distance from the search word. Correct?
Assuming so, then using a table (pub_info) in the Pubs database that is
already FT-enabled on the TEXT column (pr_info), you could use the following
SQL code to get the results you want:
-- The following SQL FTS query on the pubs table pub_info will return rows
that match the FTS search word (books)
-- and the near by words from 20 characters before to 100 characters after
the searched keyword(books).
SELECT pub_id, SubString(pr_info,PatIndex ('%books%',pr_info)-20,100)
FROM pub_info
WHERE Contains(pr_info, 'books')
/* returns the following results:
pub_id
-- ---
9952 t data for Scootney Books, publisher 9952 in the pubs database.
Scootney Books is located in New Yor
0736 t data for New Moon Books, publisher 0736 in the pubs database. New
Moon Books is located in Boston,
(2 row(s) affected)
*/
You can vary the length of the results via the PatIndex parameters.
Hopefully, this is what you're looking for!
Regards,
John
"William Wise" <will@.digitalelite.com> wrote in message
news:Xns951582AA063F8willdigitalelitecom@.68.1.17.6 ...
> I'm an FTS newb so please be gentle.
> When using most search services the results returned are accompanied by a
> snippet or excerpt of the full text that scored a hit showing the matched
> word(s)/phrase in context.
> For example, if I search for "full-text search" I might receive this as
one
> entry in the list of results:
> ... immediately alerts you if a server gets out ... a graphical
> administration interface, an SQL query tool ... It provides the full-text
> search based on Microsoft Indexing ...
> Can I get a similar snippet or excerpt using FTS or will I have to roll my
> own solution? Any suggestions regarding how to implement the latter would
> be greatly appreciated.
> Thanks,
> Will
|||Hi John,
Here's the info:
us_english
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows
NT 5.2 (Build 3790: )
Thanks for the quick reply. Works like a charm. I'll have to futze with
it some more to get it to prefix the pattern with <STRONG>tags</STRONG>.
Seems like this will require me to parse a complex user-submitted search
string if I want to show instances of each hit.
Will
"John Kane" <jt-kane@.comcast.net> wrote in
news:e6LiFCIXEHA.1684@.tk2msftngp13.phx.gbl:
> Hi Will,
> Not to worry, as I and others who post the replies here, know a great
> deal about SQL Full-text Search (FTS ;-)
> Could you post the output of the following SQL script that will
> provide info on your SQL Server and OS platform?
> use master
> go
> SELECT @.@.language
> SELECT @.@.version
> go
> If I correctly understand your requirement, you want a range of words,
> plus and minus distance from the search word. Correct?
> Assuming so, then using a table (pub_info) in the Pubs database that
> is already FT-enabled on the TEXT column (pr_info), you could use the
> following SQL code to get the results you want:
>
> -- The following SQL FTS query on the pubs table pub_info will return
> rows that match the FTS search word (books)
> -- and the near by words from 20 characters before to 100 characters
> after the searched keyword(books).
> SELECT pub_id, SubString(pr_info,PatIndex ('%books%',pr_info)-20,100)
> FROM pub_info
> WHERE Contains(pr_info, 'books')
> /* returns the following results:
> pub_id
> --
> --
> 9952 t data for Scootney Books, publisher 9952 in the pubs database.
> Scootney Books is located in New Yor
> 0736 t data for New Moon Books, publisher 0736 in the pubs database.
> New Moon Books is located in Boston,
> (2 row(s) affected)
> */
> You can vary the length of the results via the PatIndex parameters.
> Hopefully, this is what you're looking for!
> Regards,
> John
When using most search services the results returned are accompanied by a
snippet or excerpt of the full text that scored a hit showing the matched
word(s)/phrase in context.
For example, if I search for "full-text search" I might receive this as one
entry in the list of results:
... immediately alerts you if a server gets out ... a graphical
administration interface, an SQL query tool ... It provides the full-text
search based on Microsoft Indexing ...
Can I get a similar snippet or excerpt using FTS or will I have to roll my
own solution? Any suggestions regarding how to implement the latter would
be greatly appreciated.
Thanks,
Will
Hi Will,
Not to worry, as I and others who post the replies here, know a great deal
about SQL Full-text Search (FTS ;-)
Could you post the output of the following SQL script that will provide info
on your SQL Server and OS platform?
use master
go
SELECT @.@.language
SELECT @.@.version
go
If I correctly understand your requirement, you want a range of words, plus
and minus distance from the search word. Correct?
Assuming so, then using a table (pub_info) in the Pubs database that is
already FT-enabled on the TEXT column (pr_info), you could use the following
SQL code to get the results you want:
-- The following SQL FTS query on the pubs table pub_info will return rows
that match the FTS search word (books)
-- and the near by words from 20 characters before to 100 characters after
the searched keyword(books).
SELECT pub_id, SubString(pr_info,PatIndex ('%books%',pr_info)-20,100)
FROM pub_info
WHERE Contains(pr_info, 'books')
/* returns the following results:
pub_id
-- ---
9952 t data for Scootney Books, publisher 9952 in the pubs database.
Scootney Books is located in New Yor
0736 t data for New Moon Books, publisher 0736 in the pubs database. New
Moon Books is located in Boston,
(2 row(s) affected)
*/
You can vary the length of the results via the PatIndex parameters.
Hopefully, this is what you're looking for!
Regards,
John
"William Wise" <will@.digitalelite.com> wrote in message
news:Xns951582AA063F8willdigitalelitecom@.68.1.17.6 ...
> I'm an FTS newb so please be gentle.
> When using most search services the results returned are accompanied by a
> snippet or excerpt of the full text that scored a hit showing the matched
> word(s)/phrase in context.
> For example, if I search for "full-text search" I might receive this as
one
> entry in the list of results:
> ... immediately alerts you if a server gets out ... a graphical
> administration interface, an SQL query tool ... It provides the full-text
> search based on Microsoft Indexing ...
> Can I get a similar snippet or excerpt using FTS or will I have to roll my
> own solution? Any suggestions regarding how to implement the latter would
> be greatly appreciated.
> Thanks,
> Will
|||Hi John,
Here's the info:
us_english
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows
NT 5.2 (Build 3790: )
Thanks for the quick reply. Works like a charm. I'll have to futze with
it some more to get it to prefix the pattern with <STRONG>tags</STRONG>.
Seems like this will require me to parse a complex user-submitted search
string if I want to show instances of each hit.
Will
"John Kane" <jt-kane@.comcast.net> wrote in
news:e6LiFCIXEHA.1684@.tk2msftngp13.phx.gbl:
> Hi Will,
> Not to worry, as I and others who post the replies here, know a great
> deal about SQL Full-text Search (FTS ;-)
> Could you post the output of the following SQL script that will
> provide info on your SQL Server and OS platform?
> use master
> go
> SELECT @.@.language
> SELECT @.@.version
> go
> If I correctly understand your requirement, you want a range of words,
> plus and minus distance from the search word. Correct?
> Assuming so, then using a table (pub_info) in the Pubs database that
> is already FT-enabled on the TEXT column (pr_info), you could use the
> following SQL code to get the results you want:
>
> -- The following SQL FTS query on the pubs table pub_info will return
> rows that match the FTS search word (books)
> -- and the near by words from 20 characters before to 100 characters
> after the searched keyword(books).
> SELECT pub_id, SubString(pr_info,PatIndex ('%books%',pr_info)-20,100)
> FROM pub_info
> WHERE Contains(pr_info, 'books')
> /* returns the following results:
> pub_id
> --
> --
> 9952 t data for Scootney Books, publisher 9952 in the pubs database.
> Scootney Books is located in New Yor
> 0736 t data for New Moon Books, publisher 0736 in the pubs database.
> New Moon Books is located in Boston,
> (2 row(s) affected)
> */
> You can vary the length of the results via the PatIndex parameters.
> Hopefully, this is what you're looking for!
> Regards,
> John
订阅：
博文 (Atom)