Full Text Search Query

Hi,
If i write: SELECT * FROM Table1 WHERE Contains(*, ' "the" OR "horse" '), no
problem.
But the query SELECT * FROM Table1 WHERE Contains(*, ' "the" AND "horse" ')
returns an error, because the word 'the' is in the black list.
On my website, the users can check a checkbox if they want a search on all
the words they have entered.
Is there a way (something else having my own blacklist and removing by
myself the black words before sending the query to SQL Server...) to avoid
this error ?
If the only way is to use my own blacklist, is there a way to retreive the
SQL Server's one ?
Thanks.Steph wrote on Mon, 21 Mar 2005 16:34:21 +0100:

> Hi,
> If i write: SELECT * FROM Table1 WHERE Contains(*, ' "the" OR "horse" '),
> no
> problem.
> But the query SELECT * FROM Table1 WHERE Contains(*, ' "the" AND "horse"
> ') returns an error, because the word 'the' is in the black list.
> On my website, the users can check a checkbox if they want a search on all
> the words they have entered.
> Is there a way (something else having my own blacklist and removing by
> myself the black words before sending the query to SQL Server...) to avoid
> this error ?
> If the only way is to use my own blacklist, is there a way to retreive the
> SQL Server's one ?
You can remove the list of words from the SQL Server noise word file, or
remove the words from your query. I ended up clearing out my noise word
file. It depends on your installation path (and possibly SQL Server version)
as to where your noise word files are, mine are in
\MSSQL7\FTDATA\SQLServer\Config. As my server is configured for English my
noise word file is noise.enu, but I also edited noise.eng. If you decide to
remove all the noise words from the config, do not empty the file
completely - leave a single line with a space on it. You'll have to
repopulate your catalogs if you change your noise word file so that the
words you have removed are added to the catalogs.
BTW: a more appropriate group for this is
microsoft.public.sqlserver.fulltext :)
Dan|||Steph,
In addition to what Daniel says below... Yes, the full path to the noise
word files (noise.enu) is SQL Server version specific, I usually shorten it
to the files under \FTDATA\SQLServer\Config. Note, that noise.enu = US
English, noise.eng = UK English and noise.dat = Neutral and are related to
the column-specific "Language for Word Breaker" in the FT Indexing wizard.
Also, run a Full Population after modifying the noise word files.
You may also want to review KB article 246800 (Q246800) "INF: Correctly
Parsing Quotation Marks in FTS Queries" at:
http://support.microsoft.com//defau...kb;EN-US;246800
For other SQL FTS resources see "SQL Server 2000 Full-Text Search Resources
and Links" at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.e
ntry
Regards,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:um7ny3iLFHA.656@.TK2MSFTNGP14.phx.gbl...
> Steph wrote on Mon, 21 Mar 2005 16:34:21 +0100:
>
'),
all
avoid
the
> You can remove the list of words from the SQL Server noise word file, or
> remove the words from your query. I ended up clearing out my noise word
> file. It depends on your installation path (and possibly SQL Server
version)
> as to where your noise word files are, mine are in
> \MSSQL7\FTDATA\SQLServer\Config. As my server is configured for English my
> noise word file is noise.enu, but I also edited noise.eng. If you decide
to
> remove all the noise words from the config, do not empty the file
> completely - leave a single line with a space on it. You'll have to
> repopulate your catalogs if you change your noise word file so that the
> words you have removed are added to the catalogs.
> BTW: a more appropriate group for this is
> microsoft.public.sqlserver.fulltext :)
> Dan
>

Full text search and replace

Need to search through a database and remove references to a company from
it. Grrr!
How can I do this? Do I need to write a client script to load each offending
record, or can I do it with SQL?
Conceptually I want to do
REPLACE IN Resumes TEXT 'Company Name' WITH ''
This is a full text table.
whats the best approach?
I'd try something like this:
declare @.replacement varchar(200)
set @.replacement='test'
update testtext
set textcol=
substring(textcol,1, patindex('%company%',textcol)-1)
+@.replacement +
substring(textcol,patindex('%company%',textcol)+le n('company'),datalength(te
xtcol)-patindex('%company%',textcol)+len('company'))
from testtext
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Andy Gilman" <andygilman2@.hotmail.com> wrote in message
news:utvmJQubEHA.904@.TK2MSFTNGP09.phx.gbl...
> Need to search through a database and remove references to a company from
> it. Grrr!
> How can I do this? Do I need to write a client script to load each
offending
> record, or can I do it with SQL?
> Conceptually I want to do
> REPLACE IN Resumes TEXT 'Company Name' WITH ''
> This is a full text table.
> whats the best approach?
>