I have the phrase
'One Two Three'
as the value of a column that is indexed for full text searching.
I run the query
SELECT * FROM table WHERE CONTAINS(column, 'One NEAR Two' );
and it returns the row with the above value, which is all well
and fine. Now, I need it so that the row would NOT be
returned for the following query:
SELECT * FROM table WHERE CONTAINS(column, 'Two NEAR One' );
Basically, I want it so that it looks for the words NEAR each
other but only in the order specified in the query. Such that it
returns rows where the first word is NEAR the second word
but also preceeds it as well.
Looking in the documentation, I didn't see how or if this is
possible. Is it? If so, how?
thnx,
ChristophChristoph,
See my reply in the newsgroup: microsoft.public.sqlserver.fulltext.
Thanks,
John
"Christoph Boget" <jcboget@.yahoo.com> wrote in message
news:eGKLwqNyEHA.3120@.TK2MSFTNGP12.phx.gbl...
> I have the phrase
> 'One Two Three'
> as the value of a column that is indexed for full text searching.
> I run the query
> SELECT * FROM table WHERE CONTAINS(column, 'One NEAR Two' );
> and it returns the row with the above value, which is all well
> and fine. Now, I need it so that the row would NOT be
> returned for the following query:
> SELECT * FROM table WHERE CONTAINS(column, 'Two NEAR One' );
> Basically, I want it so that it looks for the words NEAR each
> other but only in the order specified in the query. Such that it
> returns rows where the first word is NEAR the second word
> but also preceeds it as well.
> Looking in the documentation, I didn't see how or if this is
> possible. Is it? If so, how?
> thnx,
> Christoph
>
>|||> See my reply in the newsgroup: microsoft.public.sqlserver.fulltext.
Thanks. I posted a follow up.
thnx,
Christoph
Showing posts with label searching. Show all posts
Showing posts with label searching. Show all posts
Saturday, February 25, 2012
Contains()
I have the phrase
'One Two Three'
as the value of a column that is indexed for full text searching.
I run the query
SELECT * FROM table WHERE CONTAINS(column, 'One NEAR Two' );
and it returns the row with the above value, which is all well
and fine. Now, I need it so that the row would NOT be
returned for the following query:
SELECT * FROM table WHERE CONTAINS(column, 'Two NEAR One' );
Basically, I want it so that it looks for the words NEAR each
other but only in the order specified in the query. Such that it
returns rows where the first word is NEAR the second word
but also preceeds it as well.
Looking in the documentation, I didn't see how or if this is
possible. Is it? If so, how?
thnx,
ChristophChristoph,
See my reply in the newsgroup: microsoft.public.sqlserver.fulltext.
Thanks,
John
"Christoph Boget" <jcboget@.yahoo.com> wrote in message
news:eGKLwqNyEHA.3120@.TK2MSFTNGP12.phx.gbl...
> I have the phrase
> 'One Two Three'
> as the value of a column that is indexed for full text searching.
> I run the query
> SELECT * FROM table WHERE CONTAINS(column, 'One NEAR Two' );
> and it returns the row with the above value, which is all well
> and fine. Now, I need it so that the row would NOT be
> returned for the following query:
> SELECT * FROM table WHERE CONTAINS(column, 'Two NEAR One' );
> Basically, I want it so that it looks for the words NEAR each
> other but only in the order specified in the query. Such that it
> returns rows where the first word is NEAR the second word
> but also preceeds it as well.
> Looking in the documentation, I didn't see how or if this is
> possible. Is it? If so, how?
> thnx,
> Christoph
>
>|||> See my reply in the newsgroup: microsoft.public.sqlserver.fulltext.
Thanks. I posted a follow up.
thnx,
Christoph
'One Two Three'
as the value of a column that is indexed for full text searching.
I run the query
SELECT * FROM table WHERE CONTAINS(column, 'One NEAR Two' );
and it returns the row with the above value, which is all well
and fine. Now, I need it so that the row would NOT be
returned for the following query:
SELECT * FROM table WHERE CONTAINS(column, 'Two NEAR One' );
Basically, I want it so that it looks for the words NEAR each
other but only in the order specified in the query. Such that it
returns rows where the first word is NEAR the second word
but also preceeds it as well.
Looking in the documentation, I didn't see how or if this is
possible. Is it? If so, how?
thnx,
ChristophChristoph,
See my reply in the newsgroup: microsoft.public.sqlserver.fulltext.
Thanks,
John
"Christoph Boget" <jcboget@.yahoo.com> wrote in message
news:eGKLwqNyEHA.3120@.TK2MSFTNGP12.phx.gbl...
> I have the phrase
> 'One Two Three'
> as the value of a column that is indexed for full text searching.
> I run the query
> SELECT * FROM table WHERE CONTAINS(column, 'One NEAR Two' );
> and it returns the row with the above value, which is all well
> and fine. Now, I need it so that the row would NOT be
> returned for the following query:
> SELECT * FROM table WHERE CONTAINS(column, 'Two NEAR One' );
> Basically, I want it so that it looks for the words NEAR each
> other but only in the order specified in the query. Such that it
> returns rows where the first word is NEAR the second word
> but also preceeds it as well.
> Looking in the documentation, I didn't see how or if this is
> possible. Is it? If so, how?
> thnx,
> Christoph
>
>|||> See my reply in the newsgroup: microsoft.public.sqlserver.fulltext.
Thanks. I posted a follow up.
thnx,
Christoph
Contains()
I have the phrase
'One Two Three'
as the value of a column that is indexed for full text searching.
I run the query
SELECT * FROM table WHERE CONTAINS(column, 'One NEAR Two' );
and it returns the row with the above value, which is all well
and fine. Now, I need it so that the row would NOT be
returned for the following query:
SELECT * FROM table WHERE CONTAINS(column, 'Two NEAR One' );
Basically, I want it so that it looks for the words NEAR each
other but only in the order specified in the query. Such that it
returns rows where the first word is NEAR the second word
but also preceeds it as well.
Looking in the documentation, I didn't see how or if this is
possible. Is it? If so, how?
thnx,
Christoph
Christoph,
See my reply in the newsgroup: microsoft.public.sqlserver.fulltext.
Thanks,
John
"Christoph Boget" <jcboget@.yahoo.com> wrote in message
news:eGKLwqNyEHA.3120@.TK2MSFTNGP12.phx.gbl...
> I have the phrase
> 'One Two Three'
> as the value of a column that is indexed for full text searching.
> I run the query
> SELECT * FROM table WHERE CONTAINS(column, 'One NEAR Two' );
> and it returns the row with the above value, which is all well
> and fine. Now, I need it so that the row would NOT be
> returned for the following query:
> SELECT * FROM table WHERE CONTAINS(column, 'Two NEAR One' );
> Basically, I want it so that it looks for the words NEAR each
> other but only in the order specified in the query. Such that it
> returns rows where the first word is NEAR the second word
> but also preceeds it as well.
> Looking in the documentation, I didn't see how or if this is
> possible. Is it? If so, how?
> thnx,
> Christoph
>
>
|||> See my reply in the newsgroup: microsoft.public.sqlserver.fulltext.
Thanks. I posted a follow up.
thnx,
Christoph
'One Two Three'
as the value of a column that is indexed for full text searching.
I run the query
SELECT * FROM table WHERE CONTAINS(column, 'One NEAR Two' );
and it returns the row with the above value, which is all well
and fine. Now, I need it so that the row would NOT be
returned for the following query:
SELECT * FROM table WHERE CONTAINS(column, 'Two NEAR One' );
Basically, I want it so that it looks for the words NEAR each
other but only in the order specified in the query. Such that it
returns rows where the first word is NEAR the second word
but also preceeds it as well.
Looking in the documentation, I didn't see how or if this is
possible. Is it? If so, how?
thnx,
Christoph
Christoph,
See my reply in the newsgroup: microsoft.public.sqlserver.fulltext.
Thanks,
John
"Christoph Boget" <jcboget@.yahoo.com> wrote in message
news:eGKLwqNyEHA.3120@.TK2MSFTNGP12.phx.gbl...
> I have the phrase
> 'One Two Three'
> as the value of a column that is indexed for full text searching.
> I run the query
> SELECT * FROM table WHERE CONTAINS(column, 'One NEAR Two' );
> and it returns the row with the above value, which is all well
> and fine. Now, I need it so that the row would NOT be
> returned for the following query:
> SELECT * FROM table WHERE CONTAINS(column, 'Two NEAR One' );
> Basically, I want it so that it looks for the words NEAR each
> other but only in the order specified in the query. Such that it
> returns rows where the first word is NEAR the second word
> but also preceeds it as well.
> Looking in the documentation, I didn't see how or if this is
> possible. Is it? If so, how?
> thnx,
> Christoph
>
>
|||> See my reply in the newsgroup: microsoft.public.sqlserver.fulltext.
Thanks. I posted a follow up.
thnx,
Christoph
Contains()
I have the phrase
'One Two Three'
as the value of a column that is indexed for full text searching.
I run the query
SELECT * FROM table WHERE CONTAINS(column, 'One NEAR Two' );
and it returns the row with the above value, which is all well
and fine. Now, I need it so that the row would NOT be
returned for the following query:
SELECT * FROM table WHERE CONTAINS(column, 'Two NEAR One' );
Basically, I want it so that it looks for the words NEAR each
other but only in the order specified in the query. Such that it
returns rows where the first word is NEAR the second word
but also preceeds it as well.
Looking in the documentation, I didn't see how or if this is
possible. Is it? If so, how?
thnx,
Christoph
Christoph,
Unfortunately, this is not possible using the SQL Server 2000 & MSSearch
service implementation of the CONTAINS* or FREETEXT* predicates. While not
documented, the FTS/MSSearch solution defines NEAR as any two words or
phrases that are *near* each other in any order, but within 50 words of each
other. At best you would need to roll your own FTS engine that identity's
the all words, as well as their sequence in the row and then develop an
index that can be queried to get your results. It is doable, but is a
non-trivial effort.
Regards,
John
"Christoph Boget" <jcboget@.yahoo.com> wrote in message
news:#2nl1dNyEHA.1392@.TK2MSFTNGP14.phx.gbl...
> I have the phrase
> 'One Two Three'
> as the value of a column that is indexed for full text searching.
> I run the query
> SELECT * FROM table WHERE CONTAINS(column, 'One NEAR Two' );
> and it returns the row with the above value, which is all well
> and fine. Now, I need it so that the row would NOT be
> returned for the following query:
> SELECT * FROM table WHERE CONTAINS(column, 'Two NEAR One' );
> Basically, I want it so that it looks for the words NEAR each
> other but only in the order specified in the query. Such that it
> returns rows where the first word is NEAR the second word
> but also preceeds it as well.
> Looking in the documentation, I didn't see how or if this is
> possible. Is it? If so, how?
> thnx,
> Christoph
>
|||> other. At best you would need to roll your own FTS engine that identity's
> the all words, as well as their sequence in the row and then develop an
> index that can be queried to get your results. It is doable, but is a
> non-trivial effort.
Actually, I found a really useful work around.
SELECT
*
FROM
table
WHERE
CONTAINS(column, 'One NEAR Two' )
AND
PATINDEX('%One%Two%', column) != 0;
Using PATINDEX() ensures that word one preceeds word two if the
words are NEAR each other.
thnx,
Christoph
|||Christoph,
A most interesting approach to this problem! While this seems to he a very
useful workaround, I've done some quick testing using SQL Server 2000 on
Win2003 with the pubs database and FT-enabled table pub_info and my results
vary somewhat. If you use "moon" and "books" as actual examples for word One
and Two respectively, and then vary the contains and patindex order, I get
the following results:
-- Test #1 with the order changed in the PATINDEX clause changed
SELECT pub_id, pr_info from pub_info -- order of PATINDEX same that order of
CONTAINS
WHERE CONTAINS(pr_info, 'books NEAR moon') AND PATINDEX('%books%moon%',
pr_info) != 0
-- returns: 1 row, pub_id = 0736 - expected results are correct.
/* -- actual text:
pub_id pr_info
-- ---
0736 This is sample text data for New Moon Books, publisher 0736 in the
pubs database. New Moon Books is
(1 row(s) affected)
*/
SELECT pub_id, pr_info from pub_info -- order of PATINDEX different that
order of CONTAINS
WHERE CONTAINS(pr_info, 'books NEAR moon') AND PATINDEX('%moon%books%',
pr_info) != 0
-- returns: 1 row, pub_id = 0736 -- expected results are not correct
-- Test #2 with the order changed in the CONTAINS clause changed
SELECT pub_id, pr_info from pub_info -- order of CONTAINS same that order of
PATINDEX, but reversed from actual order
WHERE CONTAINS(pr_info, 'moon NEAR books') AND PATINDEX('%moon%books%',
pr_info) != 0
-- returns: 1 row, pub_id = 0736 -- expected results are not correct
SELECT pub_id, pr_info from pub_info -- order of CONTAINS different that
order of PATINDEX, but reversed from actual order
WHERE CONTAINS(pr_info, 'books NEAR moon') AND PATINDEX('%moon%books%',
pr_info) != 0
-- returns: 1 row, pub_id = 0736 -- expected results are not correct
I have a feeling that while this approach may be useful, it still may be
dependent upon the actual text and what specific order the searcher is
requesting. Still a very good approach with additional refine might be most
useful!
Regards,
John
"Christoph Boget" <jcboget@.yahoo.com> wrote in message
news:OsRdnxxyEHA.3408@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
identity's
> Actually, I found a really useful work around.
> SELECT
> *
> FROM
> table
> WHERE
> CONTAINS(column, 'One NEAR Two' )
> AND
> PATINDEX('%One%Two%', column) != 0;
> Using PATINDEX() ensures that word one preceeds word two if the
> words are NEAR each other.
> thnx,
> Christoph
>
'One Two Three'
as the value of a column that is indexed for full text searching.
I run the query
SELECT * FROM table WHERE CONTAINS(column, 'One NEAR Two' );
and it returns the row with the above value, which is all well
and fine. Now, I need it so that the row would NOT be
returned for the following query:
SELECT * FROM table WHERE CONTAINS(column, 'Two NEAR One' );
Basically, I want it so that it looks for the words NEAR each
other but only in the order specified in the query. Such that it
returns rows where the first word is NEAR the second word
but also preceeds it as well.
Looking in the documentation, I didn't see how or if this is
possible. Is it? If so, how?
thnx,
Christoph
Christoph,
Unfortunately, this is not possible using the SQL Server 2000 & MSSearch
service implementation of the CONTAINS* or FREETEXT* predicates. While not
documented, the FTS/MSSearch solution defines NEAR as any two words or
phrases that are *near* each other in any order, but within 50 words of each
other. At best you would need to roll your own FTS engine that identity's
the all words, as well as their sequence in the row and then develop an
index that can be queried to get your results. It is doable, but is a
non-trivial effort.
Regards,
John
"Christoph Boget" <jcboget@.yahoo.com> wrote in message
news:#2nl1dNyEHA.1392@.TK2MSFTNGP14.phx.gbl...
> I have the phrase
> 'One Two Three'
> as the value of a column that is indexed for full text searching.
> I run the query
> SELECT * FROM table WHERE CONTAINS(column, 'One NEAR Two' );
> and it returns the row with the above value, which is all well
> and fine. Now, I need it so that the row would NOT be
> returned for the following query:
> SELECT * FROM table WHERE CONTAINS(column, 'Two NEAR One' );
> Basically, I want it so that it looks for the words NEAR each
> other but only in the order specified in the query. Such that it
> returns rows where the first word is NEAR the second word
> but also preceeds it as well.
> Looking in the documentation, I didn't see how or if this is
> possible. Is it? If so, how?
> thnx,
> Christoph
>
|||> other. At best you would need to roll your own FTS engine that identity's
> the all words, as well as their sequence in the row and then develop an
> index that can be queried to get your results. It is doable, but is a
> non-trivial effort.
Actually, I found a really useful work around.
SELECT
*
FROM
table
WHERE
CONTAINS(column, 'One NEAR Two' )
AND
PATINDEX('%One%Two%', column) != 0;
Using PATINDEX() ensures that word one preceeds word two if the
words are NEAR each other.
thnx,
Christoph
|||Christoph,
A most interesting approach to this problem! While this seems to he a very
useful workaround, I've done some quick testing using SQL Server 2000 on
Win2003 with the pubs database and FT-enabled table pub_info and my results
vary somewhat. If you use "moon" and "books" as actual examples for word One
and Two respectively, and then vary the contains and patindex order, I get
the following results:
-- Test #1 with the order changed in the PATINDEX clause changed
SELECT pub_id, pr_info from pub_info -- order of PATINDEX same that order of
CONTAINS
WHERE CONTAINS(pr_info, 'books NEAR moon') AND PATINDEX('%books%moon%',
pr_info) != 0
-- returns: 1 row, pub_id = 0736 - expected results are correct.
/* -- actual text:
pub_id pr_info
-- ---
0736 This is sample text data for New Moon Books, publisher 0736 in the
pubs database. New Moon Books is
(1 row(s) affected)
*/
SELECT pub_id, pr_info from pub_info -- order of PATINDEX different that
order of CONTAINS
WHERE CONTAINS(pr_info, 'books NEAR moon') AND PATINDEX('%moon%books%',
pr_info) != 0
-- returns: 1 row, pub_id = 0736 -- expected results are not correct
-- Test #2 with the order changed in the CONTAINS clause changed
SELECT pub_id, pr_info from pub_info -- order of CONTAINS same that order of
PATINDEX, but reversed from actual order
WHERE CONTAINS(pr_info, 'moon NEAR books') AND PATINDEX('%moon%books%',
pr_info) != 0
-- returns: 1 row, pub_id = 0736 -- expected results are not correct
SELECT pub_id, pr_info from pub_info -- order of CONTAINS different that
order of PATINDEX, but reversed from actual order
WHERE CONTAINS(pr_info, 'books NEAR moon') AND PATINDEX('%moon%books%',
pr_info) != 0
-- returns: 1 row, pub_id = 0736 -- expected results are not correct
I have a feeling that while this approach may be useful, it still may be
dependent upon the actual text and what specific order the searcher is
requesting. Still a very good approach with additional refine might be most
useful!
Regards,
John
"Christoph Boget" <jcboget@.yahoo.com> wrote in message
news:OsRdnxxyEHA.3408@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
identity's
> Actually, I found a really useful work around.
> SELECT
> *
> FROM
> table
> WHERE
> CONTAINS(column, 'One NEAR Two' )
> AND
> PATINDEX('%One%Two%', column) != 0;
> Using PATINDEX() ensures that word one preceeds word two if the
> words are NEAR each other.
> thnx,
> Christoph
>
CONTAINS simple terms search acting like prefix search.
MSSQL 2000 sp3a, on Windows 2003.
I will use examples from BOL. I am searching with a CONTAINS like this:
WHERE CONTAINS(ProductName, ' "sasquatch ale" OR "steeleye stout" ')
and am receiving results like 'sasquatch's ale' and 'steeleye's stout'.
Plurals.
Strange. I have performed a full population and this continues to
happen.
Charlie
Those aren't plurals - those are possessives.
Try "sasquatchs ale" OR "steeleyes stout"
"sqldba" <sqldba@.comcast.net> wrote in message
news:1129915220.977952.192440@.f14g2000cwb.googlegr oups.com...
> MSSQL 2000 sp3a, on Windows 2003.
> I will use examples from BOL. I am searching with a CONTAINS like this:
> WHERE CONTAINS(ProductName, ' "sasquatch ale" OR "steeleye stout" ')
> and am receiving results like 'sasquatch's ale' and 'steeleye's stout'.
> Plurals.
> Strange. I have performed a full population and this continues to
> happen.
> Charlie
>
|||Plurals - Possessives. Wither way I ask for the phrase "sasquatch ale"
and I get 'sasquatch's ale' . Why?
|||Pardon the spelling the last message. I think I may have resolved the issue.
I changed from an English[United States] Language for Word Breaker to Neutral
and it seems to work now. Not sure if this is going to introduce any new
anomalies or not but....we just testing now so bring them on.
"sqldba" wrote:
> Plurals - Possessives. Wither way I ask for the phrase "sasquatch ale"
> and I get 'sasquatch's ale' . Why?
>
I will use examples from BOL. I am searching with a CONTAINS like this:
WHERE CONTAINS(ProductName, ' "sasquatch ale" OR "steeleye stout" ')
and am receiving results like 'sasquatch's ale' and 'steeleye's stout'.
Plurals.
Strange. I have performed a full population and this continues to
happen.
Charlie
Those aren't plurals - those are possessives.
Try "sasquatchs ale" OR "steeleyes stout"
"sqldba" <sqldba@.comcast.net> wrote in message
news:1129915220.977952.192440@.f14g2000cwb.googlegr oups.com...
> MSSQL 2000 sp3a, on Windows 2003.
> I will use examples from BOL. I am searching with a CONTAINS like this:
> WHERE CONTAINS(ProductName, ' "sasquatch ale" OR "steeleye stout" ')
> and am receiving results like 'sasquatch's ale' and 'steeleye's stout'.
> Plurals.
> Strange. I have performed a full population and this continues to
> happen.
> Charlie
>
|||Plurals - Possessives. Wither way I ask for the phrase "sasquatch ale"
and I get 'sasquatch's ale' . Why?
|||Pardon the spelling the last message. I think I may have resolved the issue.
I changed from an English[United States] Language for Word Breaker to Neutral
and it seems to work now. Not sure if this is going to introduce any new
anomalies or not but....we just testing now so bring them on.
"sqldba" wrote:
> Plurals - Possessives. Wither way I ask for the phrase "sasquatch ale"
> and I get 'sasquatch's ale' . Why?
>
Friday, February 24, 2012
Contains Predicate and Double Quotes
I have been searching for an escape character or a way of escaping
double quotes that are actually in a string that I am using in the
contains predicate.
double quotes that are actually in a string that I am using in the
contains predicate.
Here is an example
select *
from table
where contains(field, '"he said "what is wrong", that is what he
said"')
I need the double quotes in the string because they are part of the
text. Of course, Fulltext search raises the error
Server: Msg 7631, Level 15, State 1, Line 1
Syntax error occurred near 'what is wrong", that is what he said'.
Expected '' in search condition '"he said "what is wrong", that is
what he said"'.
If I remove the double quotes, the search does not return the proper
results.
Thanks in advance for the help
Bill"swtwllm" <swtwllm@.alum.iup.edu> wrote in message
news:c38d6cfb.0402201036.6cdf8d87@.posting.google.c om...
> I have been searching for an escape character or a way of escaping
> double quotes that are actually in a string that I am using in the
> contains predicate.
> Here is an example
> select *
> from table
> where contains(field, '"he said "what is wrong", that is what he
> said"')
> I need the double quotes in the string because they are part of the
> text. Of course, Fulltext search raises the error
> Server: Msg 7631, Level 15, State 1, Line 1
> Syntax error occurred near 'what is wrong", that is what he said'.
> Expected '' in search condition '"he said "what is wrong", that is
> what he said"'.
> If I remove the double quotes, the search does not return the proper
> results.
> Thanks in advance for the help
> Bill
It looks like this has been answered in
microsoft.public.sqlserver.fulltext - please don't post to multiple
newsgroups separately.
Simon|||swtwllm (swtwllm@.alum.iup.edu) writes:
> I have been searching for an escape character or a way of escaping
> double quotes that are actually in a string that I am using in the
> contains predicate.
> Here is an example
> select *
> from table
> where contains(field, '"he said "what is wrong", that is what he
> said"')
> I need the double quotes in the string because they are part of the
> text. Of course, Fulltext search raises the error
> Server: Msg 7631, Level 15, State 1, Line 1
> Syntax error occurred near 'what is wrong", that is what he said'.
> Expected '' in search condition '"he said "what is wrong", that is
> what he said"'.
> If I remove the double quotes, the search does not return the proper
> results.
I would expect doubling the quotes would help, but I don't use full-text
myself, so I don't know.
microsoft.public.sqlserver.fulltext may a better place to ask.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
CONTAINS phrase behavior
Shouldn't matter.
Is the catalog up to date?
Are you searching on the data that is returned? i.e. is the match on data that you are not seeing.
It should just get the phrase.good ideas! Thank you. I just rebuilt and repopulated the index, same negative results. I deleted the index and recreated it. I doubled checked the columns that were indexed compare to the results that are returned. Nothing hidden. I tried "product select" and it returned 0, so it's not really like an AND afterall. Just a flook I guess.
I'm sure there's a user end problem in here somewhere since the query works as expected with other phrases.
Is the catalog up to date?
Are you searching on the data that is returned? i.e. is the match on data that you are not seeing.
It should just get the phrase.good ideas! Thank you. I just rebuilt and repopulated the index, same negative results. I deleted the index and recreated it. I doubled checked the columns that were indexed compare to the results that are returned. Nothing hidden. I tried "product select" and it returned 0, so it's not really like an AND afterall. Just a flook I guess.
I'm sure there's a user end problem in here somewhere since the query works as expected with other phrases.
CONTAINS in Full Text Searching
Hi
I've used CONTAINS on a varchar field.
in SQL SERVER 2000
the query was "Select name from description where CONTAINS(name,' "donot*" ')
say if I search for "Select name from description where CONTAINS(name,' "donot a*" ')
it doesn't return rows.
as might be it is seeing that 'a' as the starting letter of AND key word which is used in CONTAINS
but how to tell that it is my next letter in the search
Really I need more clarity on this...
Thanxs in advance
Could you explain what results you would expect to achieve from both queries?
Also, can you confirm that you are running both queries on SQL2k?
|||
And is only recognized by SQL Server if you specify it as a whole word (AND). A* Assumes a word with the first letter A.
Jens K. Suessmeyer
http://www.sqlserver2005.de
||||||Which wordbreaker are you using ?I'm using SQL Server 2000
say my list consists of "donot alter" word...I'm not getting that row in my output.
Jens K. Suessmeyer
http://www.sqlserver2005.de
|||I just used this query
Select name from description where CONTAINS(name,' "donot a*" ')
on the description table with full-text searching on Name field.
CONTAINS in Full Text Searching
Hi
I've used CONTAINS on a varchar field.
in SQL SERVER 2000
the query was "Select name from description where CONTAINS(name,' "donot*" ')
say if I search for "Select name from description where CONTAINS(name,' "donot a*" ')
it doesn't return rows.
as might be it is seeing that 'a' as the starting letter of AND key word which is used in CONTAINS
but how to tell that it is my next letter in the search
Really I need more clarity on this...
Thanxs in advance
Could you explain what results you would expect to achieve from both queries?
Also, can you confirm that you are running both queries on SQL2k?
|||
And is only recognized by SQL Server if you specify it as a whole word (AND). A* Assumes a word with the first letter A.
Jens K. Suessmeyer
http://www.sqlserver2005.de
||||||Which wordbreaker are you using ?I'm using SQL Server 2000
say my list consists of "donot alter" word...I'm not getting that row in my output.
Jens K. Suessmeyer
http://www.sqlserver2005.de
|||
I just used this query
Select name from description where CONTAINS(name,' "donot a*" ')
on the description table with full-text searching on Name field.
CONTAINS in Full Text Searching
Hi
I've used CONTAINS on a varchar field.
in SQL SERVER 2000
the query was "Select name from description where CONTAINS(name,' "donot*" ')
say if I search for "Select name from description where CONTAINS(name,' "donot a*" ')
it doesn't return rows.
as might be it is seeing that 'a' as the starting letter of AND key word which is used in CONTAINS
but how to tell that it is my next letter in the search
Really I need more clarity on this...
Thanxs in advance
Could you explain what results you would expect to achieve from both queries?
Also, can you confirm that you are running both queries on SQL2k?
|||
And is only recognized by SQL Server if you specify it as a whole word (AND). A* Assumes a word with the first letter A.
Jens K. Suessmeyer
http://www.sqlserver2005.de
||||||Which wordbreaker are you using ?I'm using SQL Server 2000
say my list consists of "donot alter" word...I'm not getting that row in my output.
Jens K. Suessmeyer
http://www.sqlserver2005.de
|||I just used this query
Select name from description where CONTAINS(name,' "donot a*" ')
on the description table with full-text searching on Name field.
CONTAINS clause with multi-word "AND" inflectional searching?? help...
What if you want to search using FTS with AND logic using the FORMSOF(inflectional,...) inside the CONTAINS() clause?
if my search phrase is "light hearted" I can easily do an OR search using the following in my where clause:
CONTAINS(Colname,'formsof(INFLECTIONAL,light,heart ed).
but the and is far more tricky...
does anyone know how to do this without having multiple Contains statements (which greatly increases overhead)?
I know that I can use AND in a straight contains like so:
CONTANS(column, '"light" AND "hearted"') but this does not allow me to explore inflectional variations on the words...
nesting multiple FORMSOF's doesn't seem to work either like so:
contains(column,'"formsof(inflectional,light)"' AND 'formsof(inflectional,hearted)"')
anyone else found how to do this?just to clarify I think 2 better search words for my ex. would have been "sport" and "award" and it's really important that I get results for Sports, Sported, Awards, and Awarded. Then I'd have any combination of the two words Inflectional variations (one from each root word) that exist in the same record returned in my resultset.
Subscribe to:
Posts (Atom)