Full text search?

I seem to have a problem with how Full-text search works in SQL Server 2000.
Here is an example, I have the following two "Notes" field values in two
different records:
Notes for record 1: 'hello this is a test'
Notes for record 2: 'hello again'
When I use this select statement:
select notes from DOCUMENTS_CATALOGPRODUCTS b where notes like
'%hello%'
it returns both records.
When I use this select statement with CONTAIN keyword:
select notes from DOCUMENTS_CATALOGPRODUCTS b where contains
(b.*, 'hello')
it returns only 1 record, the first one.
When I try to add the wildcard in the selection as follows:
select notes from DOCUMENTS_CATALOGPRODUCTS b where contains
(b.*, '*hello*')
it also returns only 1 record, the first one.
Therefore, this seems to be a problem with how CONTAINS keyword
works. We have to keep the 'CONTAINS' keyword because that it is
how Full-Text search works in SQL. Is this correct?
The first thing I would do is to rebuild the full text indexes... They are
not (generally) kept up to date with inserts, etc... so the ft indexes could
be behind...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:Ohr$qDXMFHA.1308@.tk2msftngp13.phx.gbl...
>I seem to have a problem with how Full-text search works in SQL Server
>2000.
> Here is an example, I have the following two "Notes" field values in two
> different records:
> Notes for record 1: 'hello this is a test'
> Notes for record 2: 'hello again'
> --
> When I use this select statement:
> select notes from DOCUMENTS_CATALOGPRODUCTS b where notes like
> '%hello%'
> it returns both records.
> --
> When I use this select statement with CONTAIN keyword:
> select notes from DOCUMENTS_CATALOGPRODUCTS b where contains
> (b.*, 'hello')
> it returns only 1 record, the first one.
> When I try to add the wildcard in the selection as follows:
> select notes from DOCUMENTS_CATALOGPRODUCTS b where contains
> (b.*, '*hello*')
> it also returns only 1 record, the first one.
> Therefore, this seems to be a problem with how CONTAINS keyword
> works. We have to keep the 'CONTAINS' keyword because that it is
> how Full-Text search works in SQL. Is this correct?
>

Full text search?

I seem to have a problem with how Full-text search works in SQL Server 2000.
Here is an example, I have the following two "Notes" field values in two
different records:
Notes for record 1: 'hello this is a test'
Notes for record 2: 'hello again'
When I use this select statement:
select notes from DOCUMENTS_CATALOGPRODUCTS b where notes like
'%hello%'
it returns both records.
--
When I use this select statement with CONTAIN keyword:
select notes from DOCUMENTS_CATALOGPRODUCTS b where contains
(b.*, 'hello')
it returns only 1 record, the first one.
---
When I try to add the wildcard in the selection as follows:
select notes from DOCUMENTS_CATALOGPRODUCTS b where contains
(b.*, '*hello*')
it also returns only 1 record, the first one.
---
Therefore, this seems to be a problem with how CONTAINS keyword
works. We have to keep the 'CONTAINS' keyword because that it is
how Full-Text search works in SQL. Is this correct?The first thing I would do is to rebuild the full text indexes... They are
not (generally) kept up to date with inserts, etc... so the ft indexes could
be behind...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:Ohr$qDXMFHA.1308@.tk2msftngp13.phx.gbl...
>I seem to have a problem with how Full-text search works in SQL Server
>2000.
> Here is an example, I have the following two "Notes" field values in two
> different records:
> Notes for record 1: 'hello this is a test'
> Notes for record 2: 'hello again'
> --
> When I use this select statement:
> select notes from DOCUMENTS_CATALOGPRODUCTS b where notes like
> '%hello%'
> it returns both records.
> --
> When I use this select statement with CONTAIN keyword:
> select notes from DOCUMENTS_CATALOGPRODUCTS b where contains
> (b.*, 'hello')
> it returns only 1 record, the first one.
> ---
> When I try to add the wildcard in the selection as follows:
> select notes from DOCUMENTS_CATALOGPRODUCTS b where contains
> (b.*, '*hello*')
> it also returns only 1 record, the first one.
> ---
> Therefore, this seems to be a problem with how CONTAINS keyword
> works. We have to keep the 'CONTAINS' keyword because that it is
> how Full-Text search works in SQL. Is this correct?
>sql

Full text search?

I seem to have a problem with how Full-text search works in SQL Server 2000.
Here is an example, I have the following two "Notes" field values in two
different records:
Notes for record 1: 'hello this is a test'
Notes for record 2: 'hello again'
--
When I use this select statement:
select notes from DOCUMENTS_CATALOGPRODUCTS b where notes like
'%hello%'
it returns both records.
--
When I use this select statement with CONTAIN keyword:
select notes from DOCUMENTS_CATALOGPRODUCTS b where contains
(b.*, 'hello')
it returns only 1 record, the first one.
---
When I try to add the wildcard in the selection as follows:
select notes from DOCUMENTS_CATALOGPRODUCTS b where contains
(b.*, '*hello*')
it also returns only 1 record, the first one.
---
Therefore, this seems to be a problem with how CONTAINS keyword
works. We have to keep the 'CONTAINS' keyword because that it is
how Full-Text search works in SQL. Is this correct?The first thing I would do is to rebuild the full text indexes... They are
not (generally) kept up to date with inserts, etc... so the ft indexes could
be behind...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:Ohr$qDXMFHA.1308@.tk2msftngp13.phx.gbl...
>I seem to have a problem with how Full-text search works in SQL Server
>2000.
> Here is an example, I have the following two "Notes" field values in two
> different records:
> Notes for record 1: 'hello this is a test'
> Notes for record 2: 'hello again'
> --
> When I use this select statement:
> select notes from DOCUMENTS_CATALOGPRODUCTS b where notes like
> '%hello%'
> it returns both records.
> --
> When I use this select statement with CONTAIN keyword:
> select notes from DOCUMENTS_CATALOGPRODUCTS b where contains
> (b.*, 'hello')
> it returns only 1 record, the first one.
> ---
> When I try to add the wildcard in the selection as follows:
> select notes from DOCUMENTS_CATALOGPRODUCTS b where contains
> (b.*, '*hello*')
> it also returns only 1 record, the first one.
> ---
> Therefore, this seems to be a problem with how CONTAINS keyword
> works. We have to keep the 'CONTAINS' keyword because that it is
> how Full-Text search works in SQL. Is this correct?
>

Full text search help

Hi I have a full text index on my product table.

When I do a search for Record, it returns all values for Record and Records.

Now If I do a search with a spelling mistake say Recod . it doen't return anything.

How can I get the full text to return my query even if there is a spelling mistake ?

Thanks

My query:

SELECT * From Product
WHERE FREETEXT (description, @.SearchString)

Try using Soundex|||

Hi dear,

You have to use LIKE operator

http://www.devguru.com/technologies/t-sql/7116.asp
http://doc.ddart.net/mssql/sql70/la-lz_2.htm

i hope these links will help you in this regard.

Thank You

Regards,

Muhammad Akhtar Shiekh

|||

Thank you both for your replied.

The thing is you cannot use LIKE or SOUNDEX in a FULL TEXT SEARCH.

|||

Hi Sleb ,

According to your question, If you want to get the full text to return your query even if there's a spelling mistake, you may achieve that through some special algorithm.

1. You may extend your keyword into several combinations.

eg:

bus -> bus array[0]
bsu array[1]
ubs array[2]
usb array[3]
sub array[4]
sbu array[5]

2. Make the select statement

string sqltxt="select * from Product where ";

for(int i=0;i<array.length;i++)
{
if( i==0 )
{
sqltxt+=" description like '% " + array[i] + " %'" ;
}
else
{
sqltxt+=" or description like '% " + array[i] + " %'" ;
}
}

In this way, you could make every combination from the original keyword match the content.

3. Make your intelligent spelling correcting more smart.

By now, you could get every combination from the original keyword, but you are not sure which one is the correct spelling. So we advice that you should build up a data table to record the user's searching behavior. eg:

keyword SubmitTimes
======================
bus 231254
bsu 12
sub 265421
sbu 125

If the user submit the keyword as "bsu", we are easy to know the possible keyword which the user wants is "bus" or "sub" but not "sbu" according to the submit times from other users.

If this does not help you, pls feel free to mark the post as Not Answered and reply. Thanks.

|||

Hi

From what I can see there is 2 methods. Method one is to use software that is available on the web to do spell checks, or you can write your own code to do a spell check. There are many text file dictionaries on the web so you do not have to be concerned about that.

I had a similar problem, but have come to an understanding that if a surfer cannot spell when surfing a website or the web then they should not be using the web. You cannot cater for every possible problem that a user can create.

I use the FREETEXT operator. What I have noticed is that it will find a plural of a word if the search is singular. For example search for "Card" will return card and cards.

I am sure by this example I think that MSSQL can be used to do a spell check. However I am not sure.

I hope this information will help you

Regard

Full text search

Hi
I am testing the usage of FULL TEXT feature and somehow it is not working
for me.
I have 2 rows in a table zz with values
this is a sta test
test
Now when I run the following query I do not get any rows back
but the full text index is used.
select zz from zz where contains ( *,'sta')
|--Hash Match(Left Semi Join, HASH:([zz].[zz])=([FULLTEXT:zz].[KEY]),
RESIDUAL:([zz].[zz]=[FULLTEXT:zz].[KEY])) 40 3 1
Hash Match Left Semi Join
HASH:([zz].[zz])=([FULLTEXT:zz].[KEY]),
RESIDUAL:([zz].[zz]=[FULLTEXT:zz].[KEY]) NULL 1.0
0.0 2.4346894E-2 36 0.42534244
[zz].[zz] NULL PLAN_ROW 0
1.0
|--Clustered Index Scan(OBJECT:([base].[dbo].[zz].[PK_zz]))
40 4 3
Clustered Index Scan Clustered Index Scan
OBJECT:([base].[dbo].[zz].[PK_zz])
[zz].[zz] 2.0 3.7578501E-2
8.0700003E-5 76 3.7659202E-2 [zz].[zz]
NULL PLAN_ROW 0 1.0
|--Remote Scan(OBJECT:(CONTAINS))Can you post the results of the following queries?
select FulltextCatalogProperty('CatalogName', 'ItemCount')
replacing CatalogName with the name of your catalog. If the value is 1 or
the number of tables you are full text indexing you will have to run a
population. If you have already run a population, check the application log
using event viewer to see if there are error messages from MSSCI or
MSSearch.
--
Hilary Cotter
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
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:3E800AE8-603F-4ADF-BCE8-1C3B27174547@.microsoft.com...
> Hi
> I am testing the usage of FULL TEXT feature and somehow it is not working
> for me.
> I have 2 rows in a table zz with values
> this is a sta test
> test
> Now when I run the following query I do not get any rows back
> but the full text index is used.
> select zz from zz where contains ( *,'sta')
> |--Hash Match(Left Semi Join, HASH:([zz].[zz])=([FULLTEXT:zz].[KEY]),
> RESIDUAL:([zz].[zz]=[FULLTEXT:zz].[KEY])) 40 3 1
> Hash Match Left Semi Join
> HASH:([zz].[zz])=([FULLTEXT:zz].[KEY]),
> RESIDUAL:([zz].[zz]=[FULLTEXT:zz].[KEY]) NULL 1.0
> 0.0 2.4346894E-2 36
0.42534244
> [zz].[zz] NULL PLAN_ROW 0
> 1.0
> |--Clustered Index Scan(OBJECT:([base].[dbo].[zz].[PK_zz]))
> 40 4 3
> Clustered Index Scan Clustered Index Scan
> OBJECT:([base].[dbo].[zz].[PK_zz])
> [zz].[zz] 2.0 3.7578501E-2
> 8.0700003E-5 76 3.7659202E-2 [zz].[zz]
> NULL PLAN_ROW 0 1.0
> |--Remote Scan(OBJECT:(CONTAINS))|||Thanks. I did check in event viewer and came across this warning when I tried
to do the full load
The crawl seed <MSSQL75://SQLServer/182c9b23> in project <SQLServer
SQL0000500005> cannot be accessed. Error: 80040e09 - Permission denied. .
also
select FulltextCatalogProperty('zz', 'ItemCount')
0
mangesh
"Hilary Cotter" wrote:
> Can you post the results of the following queries?
> select FulltextCatalogProperty('CatalogName', 'ItemCount')
> replacing CatalogName with the name of your catalog. If the value is 1 or
> the number of tables you are full text indexing you will have to run a
> population. If you have already run a population, check the application log
> using event viewer to see if there are error messages from MSSCI or
> MSSearch.
> --
> Hilary Cotter
> 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
> "Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
> message news:3E800AE8-603F-4ADF-BCE8-1C3B27174547@.microsoft.com...
> > Hi
> >
> > I am testing the usage of FULL TEXT feature and somehow it is not working
> > for me.
> > I have 2 rows in a table zz with values
> > this is a sta test
> > test
> >
> > Now when I run the following query I do not get any rows back
> > but the full text index is used.
> >
> > select zz from zz where contains ( *,'sta')
> > |--Hash Match(Left Semi Join, HASH:([zz].[zz])=([FULLTEXT:zz].[KEY]),
> > RESIDUAL:([zz].[zz]=[FULLTEXT:zz].[KEY])) 40 3 1
> > Hash Match Left Semi Join
> > HASH:([zz].[zz])=([FULLTEXT:zz].[KEY]),
> > RESIDUAL:([zz].[zz]=[FULLTEXT:zz].[KEY]) NULL 1.0
> > 0.0 2.4346894E-2 36
> 0.42534244
> > [zz].[zz] NULL PLAN_ROW 0
> > 1.0
> > |--Clustered Index Scan(OBJECT:([base].[dbo].[zz].[PK_zz]))
> > 40 4 3
> > Clustered Index Scan Clustered Index Scan
> > OBJECT:([base].[dbo].[zz].[PK_zz])
> > [zz].[zz] 2.0 3.7578501E-2
> > 8.0700003E-5 76 3.7659202E-2 [zz].[zz]
> > NULL PLAN_ROW 0 1.0
> > |--Remote Scan(OBJECT:(CONTAINS))
>
>|||The below kb addresses the most frequent cause of this problem.
http://support.microsoft.com/default.aspx?scid=kb;en-us;317746
--
Hilary Cotter
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
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:530DC499-0431-4030-A02A-5658AC5E3EE5@.microsoft.com...
> Thanks. I did check in event viewer and came across this warning when I
tried
> to do the full load
> The crawl seed <MSSQL75://SQLServer/182c9b23> in project <SQLServer
> SQL0000500005> cannot be accessed. Error: 80040e09 - Permission denied. .
> also
> select FulltextCatalogProperty('zz', 'ItemCount')
> 0
> mangesh
> "Hilary Cotter" wrote:
> > Can you post the results of the following queries?
> >
> > select FulltextCatalogProperty('CatalogName', 'ItemCount')
> >
> > replacing CatalogName with the name of your catalog. If the value is 1
or
> > the number of tables you are full text indexing you will have to run a
> > population. If you have already run a population, check the application
log
> > using event viewer to see if there are error messages from MSSCI or
> > MSSearch.
> >
> > --
> > Hilary Cotter
> > 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
> > "Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote
in
> > message news:3E800AE8-603F-4ADF-BCE8-1C3B27174547@.microsoft.com...
> > > Hi
> > >
> > > I am testing the usage of FULL TEXT feature and somehow it is not
working
> > > for me.
> > > I have 2 rows in a table zz with values
> > > this is a sta test
> > > test
> > >
> > > Now when I run the following query I do not get any rows back
> > > but the full text index is used.
> > >
> > > select zz from zz where contains ( *,'sta')
> > > |--Hash Match(Left Semi Join,
HASH:([zz].[zz])=([FULLTEXT:zz].[KEY]),
> > > RESIDUAL:([zz].[zz]=[FULLTEXT:zz].[KEY])) 40 3 1
> > > Hash Match Left Semi Join
> > > HASH:([zz].[zz])=([FULLTEXT:zz].[KEY]),
> > > RESIDUAL:([zz].[zz]=[FULLTEXT:zz].[KEY]) NULL 1.0
> > > 0.0 2.4346894E-2 36
> > 0.42534244
> > > [zz].[zz] NULL PLAN_ROW
0
> > > 1.0
> > > |--Clustered Index Scan(OBJECT:([base].[dbo].[zz].[PK_zz]))
> > > 40 4 3
> > > Clustered Index Scan Clustered Index Scan
> > > OBJECT:([base].[dbo].[zz].[PK_zz])
> > > [zz].[zz] 2.0 3.7578501E-2
> > > 8.0700003E-5 76 3.7659202E-2
[zz].[zz]
> > > NULL PLAN_ROW 0 1.0
> > > |--Remote Scan(OBJECT:(CONTAINS))
> >
> >
> >