Contains function in sql server 2005

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

djamila - 29 Nov 2006 18:53 GMT

H!

I use SQL Server 2005 and ASP.Net to program a dynamic web site. I use
full-text queries against plain character-based data with
'contains' predicat.

When my research contains more than one word separated by space (for
example: pierre baby), I have this error:

System.Data.SqlClient.SqlException: Syntax error near 'baby' in the
full-text search condition 'pierre baby'.

But the research works perfectly with (pierre|baby).

To have more user friendly tool, I would like to replace space by
"|", "&" by "+" etc.

I learn in the Internet about thesaurus function. And I performed the
following steps:

· I add to the tsGLOBAL.xml file (in ../ Microsoft SQL
Server\MSSQL.1\MSSQL\FTDATA\ directory) the lines :

<XML ID="Microsoft Search Thesaurus">

<thesaurus xmlns="x-schema:tsSchema.xml">
 <diacritics_sensitive>0</diacritics_sensitive>
 <expansion>
 
|
</expansion>
 <expansion>
+
&
</expansion>

<replacement>
<pat> </pat>
|
</replacement>
<replacement>
<pat>+</pat>
&
</replacement>
</thesaurus>

</XML>

· I modified my SQLquery like that:

dim requeteMC as string = "Select id , title from Table_V where
contains(*, 'formsof(thesaurus, " & keywords & ") ');"

But I had the same error:

System.Data.SqlClient.SqlException: Syntax error near 'baby' in the
full-text search condition 'pierre baby'.

Can any one help me about that? So when the user enter (baby Pierre)
the program converts it on baby|Pierre and we will not have an error.

Thank you very much,

regrads,

Djamila.

Reply to this Message

Arnie Rowland - 29 Nov 2006 19:15 GMT

Perhaps you get better responses by posting this to
microsoft.public.sqlserver.fulltext, which by the way, is one of the
newsgroups that you missed as you post-bombed multiple newsgroups.

Often, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to make it easier for us to
give you ideas, and to prevent folks from wasting time on already answered
questions, please:

Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup -and
you indicate that you've already posted elsewhere).

If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.

Signature

Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf

H!

I use SQL Server 2005 and ASP.Net to program a dynamic web site. I use
full-text queries against plain character-based data with
'contains' predicat.

When my research contains more than one word separated by space (for
example: pierre baby), I have this error:

System.Data.SqlClient.SqlException: Syntax error near 'baby' in the
full-text search condition 'pierre baby'.

But the research works perfectly with (pierre|baby).

To have more user friendly tool, I would like to replace space by
"|", "&" by "+" etc.

I learn in the Internet about thesaurus function. And I performed the
following steps:

· I add to the tsGLOBAL.xml file (in ../ Microsoft SQL
Server\MSSQL.1\MSSQL\FTDATA\ directory) the lines :

<XML ID="Microsoft Search Thesaurus">

<thesaurus xmlns="x-schema:tsSchema.xml">
<diacritics_sensitive>0</diacritics_sensitive>
<expansion>
 
|
</expansion>
<expansion>
+
&
</expansion>

<replacement>
<pat> </pat>
|
</replacement>
<replacement>
<pat>+</pat>
&
</replacement>
</thesaurus>

</XML>

· I modified my SQLquery like that:

dim requeteMC as string = "Select id , title from Table_V where
contains(*, 'formsof(thesaurus, " & keywords & ") ');"

But I had the same error:

System.Data.SqlClient.SqlException: Syntax error near 'baby' in the
full-text search condition 'pierre baby'.

Can any one help me about that? So when the user enter (baby Pierre)
the program converts it on baby|Pierre and we will not have an error.

Thank you very much,

regrads,

Djamila.

Reply to this Message