using contains clause

[Rob Wahmann]

Hello - I'm using the following syntax:

WHERE pages.ParentID=0 AND CONTAINS(pages.regionID,'#request.thisRegion#')

....and I get the folowing error:

[SQLServer JDBC Driver][SQLServer]Execution of a full-text operation failed.
A clause of the query contained only ignored words.

I'm trying to see if a simple column which contains comma delimited numbers
contains the variable passed as request.thisRegion. Can anyone tell me what
I'm doing wrong? I appreciate your assistance!

TIA - Rob

[Simon Hayes]

"Rob Wahmann" <ro*@dotcomstudio.biz> wrote in message
news:E8******************@newssvr26.news.prodigy.c om...

Hello - I'm using the following syntax:

WHERE pages.ParentID=0 AND CONTAINS(pages.regionID,'#request.thisRegion#')

...and I get the folowing error:

[SQLServer JDBC Driver][SQLServer]Execution of a full-text operation failed. A clause of the query contained only ignored words.

I'm trying to see if a simple column which contains comma delimited numbers contains the variable passed as request.thisRegion. Can anyone tell me what I'm doing wrong? I appreciate your assistance!

TIA - Rob

Assuming that your parameter substitution is working correctly, are you
sending a query like this to the database?

WHERE pages.ParentID=0 AND CONTAINS(pages.regionID,'1')

The digits 1 to 9 are in the ignored words file for full-text (noise.enu,
assuming you're using US English), so you may be able to get the result you
want by removing them from the file. If this doesn't help, you may want to
post in microsoft.public.sqlserver.fulltext for help.

However, if you're trying to match '1' to column values such as '1,2,3,4',
then you have a fundamental design issue. You should not have multiple
values in a single column, as this goes against basic relational design
principles. It would be better to avoid full-text searching, and instead
normalize your data model, which will be easier to work with and faster.
Although since you haven't given much background information, you may of
course have a good reason for this approach.

Simon

[Rob Wahmann]

Thanks for the quick reply. I was trying to take a shortcut by allowing the
insert of values (1,2,3,4,5, etc. from a multiple select on a site) of
region ID's into a field. I know this is probably not the best way to handle
such a task but this code will rarely be used and it's more of a 'behind the
scenes' script for building a dhtml menu. I guess I'll have to create a
relational table and insert the numbers properly. Thought it would be easy
to see if a region ID was contained within a group of numbers quickly and
easily. I appreciate the explanation.

Thanks!!!

Rob
"Simon Hayes" <sq*@hayes.ch> wrote in message
news:40**********@news.bluewin.ch...

"Rob Wahmann" <ro*@dotcomstudio.biz> wrote in message
news:E8******************@newssvr26.news.prodigy.c om...

Hello - I'm using the following syntax:

WHERE pages.ParentID=0 AND CONTAINS(pages.regionID,'#request.thisRegion#')
...and I get the folowing error:

[SQLServer JDBC Driver][SQLServer]Execution of a full-text operation failed.

A clause of the query contained only ignored words.

I'm trying to see if a simple column which contains comma delimited

numbers

contains the variable passed as request.thisRegion. Can anyone tell me

what

I'm doing wrong? I appreciate your assistance!

TIA - Rob

Assuming that your parameter substitution is working correctly, are you
sending a query like this to the database?

WHERE pages.ParentID=0 AND CONTAINS(pages.regionID,'1')

The digits 1 to 9 are in the ignored words file for full-text (noise.enu,
assuming you're using US English), so you may be able to get the result

you want by removing them from the file. If this doesn't help, you may want to
post in microsoft.public.sqlserver.fulltext for help.

However, if you're trying to match '1' to column values such as '1,2,3,4',
then you have a fundamental design issue. You should not have multiple
values in a single column, as this goes against basic relational design
principles. It would be better to avoid full-text searching, and instead
normalize your data model, which will be easier to work with and faster.
Although since you haven't given much background information, you may of
course have a good reason for this approach.

Simon

[Erland Sommarskog]

Rob Wahmann (ro*@dotcomstudio.biz) writes:

Thanks for the quick reply. I was trying to take a shortcut by allowing
the insert of values (1,2,3,4,5, etc. from a multiple select on a site)
of region ID's into a field. I know this is probably not the best way to
handle such a task but this code will rarely be used and it's more of a
'behind the scenes' script for building a dhtml menu. I guess I'll have
to create a relational table and insert the numbers properly. Thought it
would be easy to see if a region ID was contained within a group of
numbers quickly and easily. I appreciate the explanation.

Not sure if I understand exactly what you are doing, but it is possible
that you might get some ideas from an article on my web site that
discusses how to unpack comma-separated lists as tables.
http://www.sommarskog.se/arrays-in-sql.html.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Rob Wahmann]

Thanks for the info... there's some pretty good stuff in there. I went ahead
and normalized everything because that will probably be easier for me in the
short term. I'll have a look around your site! Thanks!

Rob

"Erland Sommarskog" <so****@algonet.se> wrote in message
news:Xn**********************@127.0.0.1...

Rob Wahmann (ro*@dotcomstudio.biz) writes:

Thanks for the quick reply. I was trying to take a shortcut by allowing
the insert of values (1,2,3,4,5, etc. from a multiple select on a site)
of region ID's into a field. I know this is probably not the best way to
handle such a task but this code will rarely be used and it's more of a
'behind the scenes' script for building a dhtml menu. I guess I'll have
to create a relational table and insert the numbers properly. Thought it
would be easy to see if a region ID was contained within a group of
numbers quickly and easily. I appreciate the explanation.

Not sure if I understand exactly what you are doing, but it is possible
that you might get some ideas from an article on my web site that
discusses how to unpack comma-separated lists as tables.
http://www.sommarskog.se/arrays-in-sql.html.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp