By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,152 Members | 1,027 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,152 IT Pros & Developers. It's quick & easy.

using contains clause

P: n/a
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
Jul 20 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a

"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
Jul 20 '05 #2

P: n/a
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

Jul 20 '05 #3

P: n/a
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
Jul 20 '05 #4

P: n/a
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

Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.