C# in Freetext

C# in Freetext

Probably been answered before but can't find it anywhere in these threads

We are implementing an application for a recruitment company and need to 
search for skills, we are trying to use SQL FullText Search but it refuses to 
find C# which is definitely in the database.

We have found the reason why it won't find the lowercase derivative but 
apparently C# (uppercase) should be indexed correctly and provide a match.

Has anyone come across this problem and found a solution before?

date: Tue, 6 Mar 2007 04:07:53 -0800 author: Mike Hanson

Re: C# in Freetext

Hello Mike,

Have you removed c from the noise word file? Is this sql 2000 or sql2005, 
whats the base OS.

Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons


> Probably been answered before but can't find it anywhere in these
> threads
> 
> We are implementing an application for a recruitment company and need
> to search for skills, we are trying to use SQL FullText Search but it
> refuses to find C# which is definitely in the database.
> 
> We have found the reason why it won't find the lowercase derivative
> but apparently C# (uppercase) should be indexed correctly and provide
> a match.
> 
> Has anyone come across this problem and found a solution before?
>

date: Tue, 6 Mar 2007 16:28:32 +0000 (UTC) author: Simon Sabin ail

Re: C# in Freetext

Hi Simon

Thanks for the response.

c has been removed from the noise file, and we are using SQL 2005.  The 
problem occurs in both our development combination of Developer Edition on 
Windows XP and Enterprise Edition on Windows Server 2003.

Mike

"Simon Sabin" wrote:

> Hello Mike,
> 
> Have you removed c from the noise word file? Is this sql 2000 or sql2005, 
> whats the base OS.
> 
> Simon Sabin
> SQL Server MVP
> http://sqlblogcasts.com/blogs/simons
> 
> 
> > Probably been answered before but can't find it anywhere in these
> > threads
> > 
> > We are implementing an application for a recruitment company and need
> > to search for skills, we are trying to use SQL FullText Search but it
> > refuses to find C# which is definitely in the database.
> > 
> > We have found the reason why it won't find the lowercase derivative
> > but apparently C# (uppercase) should be indexed correctly and provide
> > a match.
> > 
> > Has anyone come across this problem and found a solution before?
> > 
> 
> 
>

date: Tue, 6 Mar 2007 10:22:28 -0800 author: Mike Hanson

Re: C# in Freetext

What language are you searching in?  If you are searching using the neutral 
word breaker the noise word list controls what you see and don't see.

If you are searching in US English the C# is indexed as C# whether the c is 
in the noise word list or not.

Demo - make sure you bounce your msftesql service after making changes to 
your noise word list
--here c is in my noise word list.
select * from fulltext where contains(*,'c#')
sp_help_fulltexttable fulltext
drop table testtable2
create table testtable2(pk int identity not null constraint testtable2pk 
primary key,
charcol char(10))
insert into testtable2 (charcol) values('c')
insert into testtable2 (charcol) values('c!')
insert into testtable2 (charcol) values('c#')
insert into testtable2 (charcol) values('c+')
insert into testtable2 (charcol) values('c++')
insert into testtable2 (charcol) values('C')
insert into testtable2 (charcol) values('C!')
insert into testtable2 (charcol) values('C#')
insert into testtable2 (charcol) values('C+')
insert into testtable2 (charcol) values('C++')
go
select * from testtable2
go
create fulltext index on testtable2(charcol language 0) key index 
testtable2pk
GO

select * from testtable2 where contains(*,'C#')
go
--Informational: The full-text search condition contained noise word(s).
--nothing found
select * from testtable2 where contains(*,'c#')
go
--Informational: The full-text search condition contained noise word(s).
--nothing found

drop fulltext index on testtable2
GO
create fulltext index on testtable2(charcol language 1033) key index 
testtable2pk
GO
select * from testtable2 where contains(*,'C#')
go
--row found - upper case C#
select * from testtable2 where contains(*,'c#')
go
-- no rows found

Now if I remove c from my noise word list I get identical results except for 
the query on lower case c# where I get all the rows returned EXCEPT upper 
case C#.

-- 
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



"Mike Hanson"  wrote in message 
news:0C6BF833-B40E-4AAD-86D4-5672C6F4AF91@microsoft.com...
> Probably been answered before but can't find it anywhere in these threads
>
> We are implementing an application for a recruitment company and need to
> search for skills, we are trying to use SQL FullText Search but it refuses 
> to
> find C# which is definitely in the database.
>
> We have found the reason why it won't find the lowercase derivative but
> apparently C# (uppercase) should be indexed correctly and provide a match.
>
> Has anyone come across this problem and found a solution before?

date: Tue, 6 Mar 2007 15:49:01 -0500 author: Hilary Cotter

Re: C# in Freetext

Thanks Hilary, it was indeed the language issue, as soon as we switched from 
the Neutral language we got what we want.

Your help is greatly appreciated.

Mike

"Hilary Cotter" wrote:

> What language are you searching in?  If you are searching using the neutral 
> word breaker the noise word list controls what you see and don't see.
> 
> If you are searching in US English the C# is indexed as C# whether the c is 
> in the noise word list or not.
> 
> Demo - make sure you bounce your msftesql service after making changes to 
> your noise word list
> --here c is in my noise word list.
> select * from fulltext where contains(*,'c#')
> sp_help_fulltexttable fulltext
> drop table testtable2
> create table testtable2(pk int identity not null constraint testtable2pk 
> primary key,
> charcol char(10))
> insert into testtable2 (charcol) values('c')
> insert into testtable2 (charcol) values('c!')
> insert into testtable2 (charcol) values('c#')
> insert into testtable2 (charcol) values('c+')
> insert into testtable2 (charcol) values('c++')
> insert into testtable2 (charcol) values('C')
> insert into testtable2 (charcol) values('C!')
> insert into testtable2 (charcol) values('C#')
> insert into testtable2 (charcol) values('C+')
> insert into testtable2 (charcol) values('C++')
> go
> select * from testtable2
> go
> create fulltext index on testtable2(charcol language 0) key index 
> testtable2pk
> GO
> 
> select * from testtable2 where contains(*,'C#')
> go
> --Informational: The full-text search condition contained noise word(s).
> --nothing found
> select * from testtable2 where contains(*,'c#')
> go
> --Informational: The full-text search condition contained noise word(s).
> --nothing found
> 
> drop fulltext index on testtable2
> GO
> create fulltext index on testtable2(charcol language 1033) key index 
> testtable2pk
> GO
> select * from testtable2 where contains(*,'C#')
> go
> --row found - upper case C#
> select * from testtable2 where contains(*,'c#')
> go
> -- no rows found
> 
> Now if I remove c from my noise word list I get identical results except for 
> the query on lower case c# where I get all the rows returned EXCEPT upper 
> case C#.
> 
> -- 
> 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
> 
> 
> 
> "Mike Hanson"  wrote in message 
> news:0C6BF833-B40E-4AAD-86D4-5672C6F4AF91@microsoft.com...
> > Probably been answered before but can't find it anywhere in these threads
> >
> > We are implementing an application for a recruitment company and need to
> > search for skills, we are trying to use SQL FullText Search but it refuses 
> > to
> > find C# which is definitely in the database.
> >
> > We have found the reason why it won't find the lowercase derivative but
> > apparently C# (uppercase) should be indexed correctly and provide a match.
> >
> > Has anyone come across this problem and found a solution before? 
> 
> 
>

date: Wed, 7 Mar 2007 06:23:22 -0800 author: Mike Hanson