Hello all,
it seems to me that ranking of full-text search results does not work
correctly. Here's my scenario:
- one table with a full-text indexed text column and several full-text
indexed meta data columns
- another table with a full-text indexed image column containing .pdf,
..gif, and .jpg files; full-text indexed meta data columns as in the
first table
- a stored procedure that makes a full-text search to the two
full-text indexes (using containstable) and combines the result sets
-Windows 2000 Server, SQL Server 2000 sp3, Adobe PDF iFilter
Now, the search itself seems work as expected. All the rows that
should be returned seem to be returned. The problem is the ranking
with .pdf files: all the .pdf files seem to get a rank value far too
high. A simplified example demonstrates the problem.
TABLE 1
nvc_Heading nvc_Subject txt_Data
Talentless hack missing (long text)
TABLE 2
nvc_Heading nvc_Subject img_Data
Talentless hack missing (binary data)
Making a containstable query to both tables with search term
"talentless hack", the search result from table 1 gets a significantly
lower rank than the result from table 2. It seems that data in column
img_Data is not processed when determining the rank value.
Anyone come across this problem? TIA,
sdr
Recently indexed rows get a higher ranking than rows that have been indexed previously.
Can you change your system time to 1 minute before midnight, wait till the midnight merge occurs and then check to see if the ranking is more consistent.
|||SDR,
Can you confirm if you have "Change Tracking" with "Update Index in
Background" enabled for your FT-enable tables (TABLE 1 and TABLE 2 in your
example)? Also, can you tell us how many rows are in each of these table and
if you're using one FT Catalog for both tables or have one FT Catalog for
each table?
While Hilary suggests changing your server system time, I do not think this
is a good idea as such a change will affect other processes on this server
as well as any date/time sensitive processing for SQL Server table with
datetime columns. An alternative is to change the following Registry key
value (replace <physical_FT_Catalog> your actual FT Catalog folder, for
example: SQL0000500005)
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0Ca talogNames\SQLServer\<phys
ical_FT_Catalog>
indexer:ci:MidNightMasterMergeTimeDelta value= <time>
<time> is Time, in minutes, after midnight at which a master merge will
occur. and the default value is 0. Setting <time> to 60, would force the
Master Merge to occur at 1am.
Regards,
John
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:51F282E4-24EA-4A61-B4A5-CD8B5FA0F622@.microsoft.com...
> Recently indexed rows get a higher ranking than rows that have been
indexed previously.
> Can you change your system time to 1 minute before midnight, wait till the
midnight merge occurs and then check to see if the ranking is more
consistent.
|||Oops, it looks like this problem was fixed with Sp3.
Sorry about that.
Hilary
|||John,
I tried changing the suggested registry value, but no change in
ranking values took place - actually I didn't notice any merge taking
place either.
Change Tracking is not enabled in my database, but I'm running a full
population to both catalogs every night. I suppose the master merge is
done right after the full population, am I correct?
Both tables have their own, dedicated catalogs. There are approx.
15000-20000 rows in each table.
Any more ideas?
sdr
"John Kane" <jt-kane@.comcast.net> wrote in message news:<#IhuyMWOEHA.3312@.tk2msftngp13.phx.gbl>...
> SDR,
> Can you confirm if you have "Change Tracking" with "Update Index in
> Background" enabled for your FT-enable tables (TABLE 1 and TABLE 2 in your
> example)? Also, can you tell us how many rows are in each of these table and
> if you're using one FT Catalog for both tables or have one FT Catalog for
> each table?
> While Hilary suggests changing your server system time, I do not think this
> is a good idea as such a change will affect other processes on this server
> as well as any date/time sensitive processing for SQL Server table with
> datetime columns. An alternative is to change the following Registry key
> value (replace <physical_FT_Catalog> your actual FT Catalog folder, for
> example: SQL0000500005)
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0Ca talogNames\SQLServer\<phys
> ical_FT_Catalog>
> indexer:ci:MidNightMasterMergeTimeDelta value= <time>
>
> <time> is Time, in minutes, after midnight at which a master merge will
> occur. and the default value is 0. Setting <time> to 60, would force the
> Master Merge to occur at 1am.
>
> Regards,
> John
>
>
>
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:51F282E4-24EA-4A61-B4A5-CD8B5FA0F622@.microsoft.com...
> indexed previously.
> midnight merge occurs and then check to see if the ranking is more
> consistent.
|||sdr,
You may have to stop & restart the MSSearch service, for the master merger
change to be effective with changing the registry key value. Could you post
the actual FTS query (containstable or freetexttable) and the rank value
along with the full output of -- SELECT @.@.version -- as this will provide
both SQL Server and OS platform version info.
Regards,
John
"sdr" <viiksi_reima@.hotmail.com> wrote in message
news:3652826d.0405180353.6d3c5ef0@.posting.google.c om...
> John,
> I tried changing the suggested registry value, but no change in
> ranking values took place - actually I didn't notice any merge taking
> place either.
> Change Tracking is not enabled in my database, but I'm running a full
> population to both catalogs every night. I suppose the master merge is
> done right after the full population, am I correct?
> Both tables have their own, dedicated catalogs. There are approx.
> 15000-20000 rows in each table.
> Any more ideas?
> sdr
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
news:<#IhuyMWOEHA.3312@.tk2msftngp13.phx.gbl>...[vbcol=seagreen]
your[vbcol=seagreen]
and[vbcol=seagreen]
for[vbcol=seagreen]
this[vbcol=seagreen]
server[vbcol=seagreen]
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0Ca talogNames\SQLServer\<phys[vbcol=seagreen]
the[vbcol=seagreen]
Showing posts with label indexed. Show all posts
Showing posts with label indexed. 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,
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 with substring
I have a table field that is full-text indexed. I am trying to locate
records where a substring of data is present in a string without spaces. The
format is something like this:
"AAAAAABBAAAA"
If I am looking for the existence of "AABBA" in the string, CONTAINS
apparently will not work, because this command works only with complete words
with spaces between. So this type of query fails:
CONTAINS(fieldname, "AABBA")
or
CONTAINS(SUBSTRING(fieldname, 5,5), "AABBA")
Is there a way to use CONTAINS with substrings like this?
seeker
No, the only thing you can do is store the string in reverse in your table
you are FTI'ing, and then reserve the search string and do wildcarding. This
only works if you are searching for suffixes, not letter patterns in the
middle of a word/token.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"seeker" <seeker@.discussions.microsoft.com> wrote in message
news:3F5BA0DE-AC1B-44A0-886E-8D54E2AFC3A0@.microsoft.com...
> I have a table field that is full-text indexed. I am trying to locate
> records where a substring of data is present in a string without spaces.
The
> format is something like this:
> "AAAAAABBAAAA"
> If I am looking for the existence of "AABBA" in the string, CONTAINS
> apparently will not work, because this command works only with complete
words
> with spaces between. So this type of query fails:
> CONTAINS(fieldname, "AABBA")
> or
> CONTAINS(SUBSTRING(fieldname, 5,5), "AABBA")
> Is there a way to use CONTAINS with substrings like this?
> --
> seeker
records where a substring of data is present in a string without spaces. The
format is something like this:
"AAAAAABBAAAA"
If I am looking for the existence of "AABBA" in the string, CONTAINS
apparently will not work, because this command works only with complete words
with spaces between. So this type of query fails:
CONTAINS(fieldname, "AABBA")
or
CONTAINS(SUBSTRING(fieldname, 5,5), "AABBA")
Is there a way to use CONTAINS with substrings like this?
seeker
No, the only thing you can do is store the string in reverse in your table
you are FTI'ing, and then reserve the search string and do wildcarding. This
only works if you are searching for suffixes, not letter patterns in the
middle of a word/token.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"seeker" <seeker@.discussions.microsoft.com> wrote in message
news:3F5BA0DE-AC1B-44A0-886E-8D54E2AFC3A0@.microsoft.com...
> I have a table field that is full-text indexed. I am trying to locate
> records where a substring of data is present in a string without spaces.
The
> format is something like this:
> "AAAAAABBAAAA"
> If I am looking for the existence of "AABBA" in the string, CONTAINS
> apparently will not work, because this command works only with complete
words
> with spaces between. So this type of query fails:
> CONTAINS(fieldname, "AABBA")
> or
> CONTAINS(SUBSTRING(fieldname, 5,5), "AABBA")
> Is there a way to use CONTAINS with substrings like this?
> --
> seeker
CONTAINS with AND across multiple Columns
How come when I am doing a CONTAINS search across multiple columns on
a table that I have full text indexed I don't get any matches when one
word is contained in one column and the other word is contained in the
other column in the same row of data? Here is a query where first
name is in one column and last name is in another column. Is the only
option to physically store this information concatenated together so
my search will behave as expected?
SELECT *
FROM dbo.Person
WHERE CONTAINS ((FIRST_NAME,LAST_NAME),'"BARRY*" AND "SMITH*"')
This is by design. In SQL 2000 a freetext search could look across columns.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Spencer" <spencer@.tabbert.net> wrote in message
news:227246c4-bc9e-40b8-a64b-1dd2c2ae8df6@.g21g2000hsh.googlegroups.com...
> How come when I am doing a CONTAINS search across multiple columns on
> a table that I have full text indexed I don't get any matches when one
> word is contained in one column and the other word is contained in the
> other column in the same row of data? Here is a query where first
> name is in one column and last name is in another column. Is the only
> option to physically store this information concatenated together so
> my search will behave as expected?
> SELECT *
> FROM dbo.Person
> WHERE CONTAINS ((FIRST_NAME,LAST_NAME),'"BARRY*" AND "SMITH*"')
a table that I have full text indexed I don't get any matches when one
word is contained in one column and the other word is contained in the
other column in the same row of data? Here is a query where first
name is in one column and last name is in another column. Is the only
option to physically store this information concatenated together so
my search will behave as expected?
SELECT *
FROM dbo.Person
WHERE CONTAINS ((FIRST_NAME,LAST_NAME),'"BARRY*" AND "SMITH*"')
This is by design. In SQL 2000 a freetext search could look across columns.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Spencer" <spencer@.tabbert.net> wrote in message
news:227246c4-bc9e-40b8-a64b-1dd2c2ae8df6@.g21g2000hsh.googlegroups.com...
> How come when I am doing a CONTAINS search across multiple columns on
> a table that I have full text indexed I don't get any matches when one
> word is contained in one column and the other word is contained in the
> other column in the same row of data? Here is a query where first
> name is in one column and last name is in another column. Is the only
> option to physically store this information concatenated together so
> my search will behave as expected?
> SELECT *
> FROM dbo.Person
> WHERE CONTAINS ((FIRST_NAME,LAST_NAME),'"BARRY*" AND "SMITH*"')
Friday, February 24, 2012
Contains 2 Column and / or FTS
Thanks for any help!
SQL 2000 Standard Edition with SP3. The title and document column are both
Full text indexed. The problem I'm trying to solve is how to get the below
stored procedure to search BOTH columns for the criteria entered.
Search Example:
"pin*" or "Yello*" Returns 2 rows as desired.
"pin*" and "yello*" Returns no results which is not desired.
However "Pin*" and "Gree*" does return results which is also desired.
I'm trying to build a procedure which will pull matched words from BOTH
columns for both the "AND" and "OR" operators. Maybe it's the way the FTS
work in Microsoft, but currently when entering the "pin*" and "yello*"
search, it looks for both those terms in the SAME colum, and will not join
them so to speak.
A FreeText search would always return me the desired results, except it
does not seem to do partial term searches or wildcards, unless I'm missing
something.
I've found and seen quite a few bits of code on the internet such as this
http://www.experts-exchange.com/Data...20705253.html, but they don't seem to quite fit my needs.
Thanks a million!!
CREATE TABLE [dbo].[Document] (
[DocumentID] [int] IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (100) ,
[Submitter] [varchar] (100) NOT NULL ,
[RevisedBy] [varchar] (100) NOT NULL ,
[Document] [text] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
insert into document(Title, submitter, Revisedby, document) values ('Pink
Green',
'John Henry', 'Steven Smith', 'Orange')
insert into document(Title, submitter, Revisedby, document) values ('Silver
Blue',
'Kelly Owen', 'John Henry', 'Yellow')
GO
ALTER PROCEDURE SearchDoc2
(
@.Word varchar (300) = Null,
@.SubmitterID varchar (100) = NULL,
@.systemID varchar (100) = NULL
)
as
SELECT Title,
SubmitterID,
RevisedBy
FROM tblDocument
where (Contains(*, @.word) or @.Word IS NULL)
and
(systemID = @.SystemID or @.SystemID IS Null) and
((SubmitterID = @.SubmitterID or @.SubmitterID IS NULL) or
(RevisedBy = @.SubmitterID or @.SubmitterID IS NULL) )
Jeff,
Have you considered using CONTAINSTABLE vs. CONTAINS? If not, please review:
SQL Server FTS across multiple tables or columns
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.entry
Below are modified code examples from the above blog entry to demonstrate
how to handle both the AND or OR operators. Note, you and use dynamic SQL
code in the WHERE clause to handle both conditions in one stored proc. Also,
keep in mind that with the use of two CONTAINSTABLE clauses, this may not
perform well against large (>1 million rows) FT-enabled tables as each
CONTAINSTABLE clause is a full "round-trip" to the FT Catalog.
use Northwind
SELECT e.LastName
from Employees AS e,
containstable(Employees, Notes, 'ISABOUT (BA weight (.2) )') as A,
containstable(Employees, Title, 'Sales') as B
where
A.[KEY] = e.EmployeeID and -- Note, AND operator.
B.[KEY] = e.EmployeeID
-- vs.
use Northwind
SELECT distinct e.LastName
from Employees AS e,
containstable(Employees, Notes, 'ISABOUT (BA weight (.2) )') as A,
containstable(Employees, Title, 'Sales') as B
where
A.[KEY] = e.EmployeeID or -- Note, OR operator.
B.[KEY] = e.EmployeeID
For the OR operator, you will need to add the DISTINCT operator to prevent
dups in your results.
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:F5A5A066-BE17-43CC-83E3-E65E6270AC99@.microsoft.com...
> Thanks for any help!
> SQL 2000 Standard Edition with SP3. The title and document column are
> both
> Full text indexed. The problem I'm trying to solve is how to get the
> below
> stored procedure to search BOTH columns for the criteria entered.
> Search Example:
> "pin*" or "Yello*" Returns 2 rows as desired.
> "pin*" and "yello*" Returns no results which is not desired.
> However "Pin*" and "Gree*" does return results which is also desired.
> I'm trying to build a procedure which will pull matched words from BOTH
> columns for both the "AND" and "OR" operators. Maybe it's the way the FTS
> work in Microsoft, but currently when entering the "pin*" and "yello*"
> search, it looks for both those terms in the SAME colum, and will not join
> them so to speak.
> A FreeText search would always return me the desired results, except it
> does not seem to do partial term searches or wildcards, unless I'm missing
> something.
> I've found and seen quite a few bits of code on the internet such as this
> http://www.experts-exchange.com/Data...20705253.html,
> but they don't seem to quite fit my needs.
> Thanks a million!!
> CREATE TABLE [dbo].[Document] (
> [DocumentID] [int] IDENTITY (1, 1) NOT NULL ,
> [Title] [varchar] (100) ,
> [Submitter] [varchar] (100) NOT NULL ,
> [RevisedBy] [varchar] (100) NOT NULL ,
> [Document] [text] NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> insert into document(Title, submitter, Revisedby, document) values ('Pink
> Green',
> 'John Henry', 'Steven Smith', 'Orange')
> insert into document(Title, submitter, Revisedby, document) values
> ('Silver
> Blue',
> 'Kelly Owen', 'John Henry', 'Yellow')
> GO
>
> ALTER PROCEDURE SearchDoc2
> (
> @.Word varchar (300) = Null,
> @.SubmitterID varchar (100) = NULL,
> @.systemID varchar (100) = NULL
> )
> as
>
> SELECT Title,
> SubmitterID,
> RevisedBy
>
> FROM tblDocument
> where (Contains(*, @.word) or @.Word IS NULL)
> and
> (systemID = @.SystemID or @.SystemID IS Null) and
> ((SubmitterID = @.SubmitterID or @.SubmitterID IS NULL) or
> (RevisedBy = @.SubmitterID or @.SubmitterID IS NULL) )
>
>
|||Thanks for the reference John.
I read it over well and tried the examples which I've somewhat tried before.
Aparently when using the [and] operator with contains[table], it looks for
both "words" to exist in the same column, and not scross columns.
I think perhaps the FreeTextable will suit our needs, it's just a shame that
it does not support wildcards yet.
Thanks,
Jeff
"John Kane" wrote:
> Jeff,
> Have you considered using CONTAINSTABLE vs. CONTAINS? If not, please review:
> SQL Server FTS across multiple tables or columns
> http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.entry
> Below are modified code examples from the above blog entry to demonstrate
> how to handle both the AND or OR operators. Note, you and use dynamic SQL
> code in the WHERE clause to handle both conditions in one stored proc. Also,
> keep in mind that with the use of two CONTAINSTABLE clauses, this may not
> perform well against large (>1 million rows) FT-enabled tables as each
> CONTAINSTABLE clause is a full "round-trip" to the FT Catalog.
> use Northwind
> SELECT e.LastName
> from Employees AS e,
> containstable(Employees, Notes, 'ISABOUT (BA weight (.2) )') as A,
> containstable(Employees, Title, 'Sales') as B
> where
> A.[KEY] = e.EmployeeID and -- Note, AND operator.
> B.[KEY] = e.EmployeeID
> -- vs.
> use Northwind
> SELECT distinct e.LastName
> from Employees AS e,
> containstable(Employees, Notes, 'ISABOUT (BA weight (.2) )') as A,
> containstable(Employees, Title, 'Sales') as B
> where
> A.[KEY] = e.EmployeeID or -- Note, OR operator.
> B.[KEY] = e.EmployeeID
> For the OR operator, you will need to add the DISTINCT operator to prevent
> dups in your results.
> Hope that helps!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
> news:F5A5A066-BE17-43CC-83E3-E65E6270AC99@.microsoft.com...
>
>
|||Jeff wrote on Mon, 19 Sep 2005 11:19:06 -0700:
> Thanks for the reference John.
> I read it over well and tried the examples which I've somewhat tried
> before.
> Aparently when using the [and] operator with contains[table], it looks for
> both "words" to exist in the same column, and not scross columns.
> I think perhaps the FreeTextable will suit our needs, it's just a shame
> that it does not support wildcards yet.
You can achieve your result using Contains, but it requires more processing:
select * from mytable where contains(*,'"pin*"') and contains(*,'"yellow*"')
This will find all rows with pin* in the FTS index, and all rows with
yello*, and then combine them together to find all rows that have both terms
in either column.
However, from a performance point of view this may not be satisfactory as
there is an FTS search for each word rather than all at once. In my own
system I maintain an extra column that is a contenation of the 6 columns
that I use in my searches - so if I want a search just on book titles I
would use Contains(title,@.words) but if I wanted the words to appear in any
columns then I'd use Contains(keywords,@.words). It does mean that when data
is changed the keywords field needs to be rebuilt, but all data is processed
from files via an in-house application so this is done automatically.
Additional storage is required for the extra column, but I'd rather use up a
bit more of my disk space than have slow FTS results (disk space is after
all relatively cheap compared to the perceived performance of a web site
search by customers).
Dan
SQL 2000 Standard Edition with SP3. The title and document column are both
Full text indexed. The problem I'm trying to solve is how to get the below
stored procedure to search BOTH columns for the criteria entered.
Search Example:
"pin*" or "Yello*" Returns 2 rows as desired.
"pin*" and "yello*" Returns no results which is not desired.
However "Pin*" and "Gree*" does return results which is also desired.
I'm trying to build a procedure which will pull matched words from BOTH
columns for both the "AND" and "OR" operators. Maybe it's the way the FTS
work in Microsoft, but currently when entering the "pin*" and "yello*"
search, it looks for both those terms in the SAME colum, and will not join
them so to speak.
A FreeText search would always return me the desired results, except it
does not seem to do partial term searches or wildcards, unless I'm missing
something.
I've found and seen quite a few bits of code on the internet such as this
http://www.experts-exchange.com/Data...20705253.html, but they don't seem to quite fit my needs.
Thanks a million!!
CREATE TABLE [dbo].[Document] (
[DocumentID] [int] IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (100) ,
[Submitter] [varchar] (100) NOT NULL ,
[RevisedBy] [varchar] (100) NOT NULL ,
[Document] [text] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
insert into document(Title, submitter, Revisedby, document) values ('Pink
Green',
'John Henry', 'Steven Smith', 'Orange')
insert into document(Title, submitter, Revisedby, document) values ('Silver
Blue',
'Kelly Owen', 'John Henry', 'Yellow')
GO
ALTER PROCEDURE SearchDoc2
(
@.Word varchar (300) = Null,
@.SubmitterID varchar (100) = NULL,
@.systemID varchar (100) = NULL
)
as
SELECT Title,
SubmitterID,
RevisedBy
FROM tblDocument
where (Contains(*, @.word) or @.Word IS NULL)
and
(systemID = @.SystemID or @.SystemID IS Null) and
((SubmitterID = @.SubmitterID or @.SubmitterID IS NULL) or
(RevisedBy = @.SubmitterID or @.SubmitterID IS NULL) )
Jeff,
Have you considered using CONTAINSTABLE vs. CONTAINS? If not, please review:
SQL Server FTS across multiple tables or columns
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.entry
Below are modified code examples from the above blog entry to demonstrate
how to handle both the AND or OR operators. Note, you and use dynamic SQL
code in the WHERE clause to handle both conditions in one stored proc. Also,
keep in mind that with the use of two CONTAINSTABLE clauses, this may not
perform well against large (>1 million rows) FT-enabled tables as each
CONTAINSTABLE clause is a full "round-trip" to the FT Catalog.
use Northwind
SELECT e.LastName
from Employees AS e,
containstable(Employees, Notes, 'ISABOUT (BA weight (.2) )') as A,
containstable(Employees, Title, 'Sales') as B
where
A.[KEY] = e.EmployeeID and -- Note, AND operator.
B.[KEY] = e.EmployeeID
-- vs.
use Northwind
SELECT distinct e.LastName
from Employees AS e,
containstable(Employees, Notes, 'ISABOUT (BA weight (.2) )') as A,
containstable(Employees, Title, 'Sales') as B
where
A.[KEY] = e.EmployeeID or -- Note, OR operator.
B.[KEY] = e.EmployeeID
For the OR operator, you will need to add the DISTINCT operator to prevent
dups in your results.
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:F5A5A066-BE17-43CC-83E3-E65E6270AC99@.microsoft.com...
> Thanks for any help!
> SQL 2000 Standard Edition with SP3. The title and document column are
> both
> Full text indexed. The problem I'm trying to solve is how to get the
> below
> stored procedure to search BOTH columns for the criteria entered.
> Search Example:
> "pin*" or "Yello*" Returns 2 rows as desired.
> "pin*" and "yello*" Returns no results which is not desired.
> However "Pin*" and "Gree*" does return results which is also desired.
> I'm trying to build a procedure which will pull matched words from BOTH
> columns for both the "AND" and "OR" operators. Maybe it's the way the FTS
> work in Microsoft, but currently when entering the "pin*" and "yello*"
> search, it looks for both those terms in the SAME colum, and will not join
> them so to speak.
> A FreeText search would always return me the desired results, except it
> does not seem to do partial term searches or wildcards, unless I'm missing
> something.
> I've found and seen quite a few bits of code on the internet such as this
> http://www.experts-exchange.com/Data...20705253.html,
> but they don't seem to quite fit my needs.
> Thanks a million!!
> CREATE TABLE [dbo].[Document] (
> [DocumentID] [int] IDENTITY (1, 1) NOT NULL ,
> [Title] [varchar] (100) ,
> [Submitter] [varchar] (100) NOT NULL ,
> [RevisedBy] [varchar] (100) NOT NULL ,
> [Document] [text] NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> insert into document(Title, submitter, Revisedby, document) values ('Pink
> Green',
> 'John Henry', 'Steven Smith', 'Orange')
> insert into document(Title, submitter, Revisedby, document) values
> ('Silver
> Blue',
> 'Kelly Owen', 'John Henry', 'Yellow')
> GO
>
> ALTER PROCEDURE SearchDoc2
> (
> @.Word varchar (300) = Null,
> @.SubmitterID varchar (100) = NULL,
> @.systemID varchar (100) = NULL
> )
> as
>
> SELECT Title,
> SubmitterID,
> RevisedBy
>
> FROM tblDocument
> where (Contains(*, @.word) or @.Word IS NULL)
> and
> (systemID = @.SystemID or @.SystemID IS Null) and
> ((SubmitterID = @.SubmitterID or @.SubmitterID IS NULL) or
> (RevisedBy = @.SubmitterID or @.SubmitterID IS NULL) )
>
>
|||Thanks for the reference John.
I read it over well and tried the examples which I've somewhat tried before.
Aparently when using the [and] operator with contains[table], it looks for
both "words" to exist in the same column, and not scross columns.
I think perhaps the FreeTextable will suit our needs, it's just a shame that
it does not support wildcards yet.
Thanks,
Jeff
"John Kane" wrote:
> Jeff,
> Have you considered using CONTAINSTABLE vs. CONTAINS? If not, please review:
> SQL Server FTS across multiple tables or columns
> http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.entry
> Below are modified code examples from the above blog entry to demonstrate
> how to handle both the AND or OR operators. Note, you and use dynamic SQL
> code in the WHERE clause to handle both conditions in one stored proc. Also,
> keep in mind that with the use of two CONTAINSTABLE clauses, this may not
> perform well against large (>1 million rows) FT-enabled tables as each
> CONTAINSTABLE clause is a full "round-trip" to the FT Catalog.
> use Northwind
> SELECT e.LastName
> from Employees AS e,
> containstable(Employees, Notes, 'ISABOUT (BA weight (.2) )') as A,
> containstable(Employees, Title, 'Sales') as B
> where
> A.[KEY] = e.EmployeeID and -- Note, AND operator.
> B.[KEY] = e.EmployeeID
> -- vs.
> use Northwind
> SELECT distinct e.LastName
> from Employees AS e,
> containstable(Employees, Notes, 'ISABOUT (BA weight (.2) )') as A,
> containstable(Employees, Title, 'Sales') as B
> where
> A.[KEY] = e.EmployeeID or -- Note, OR operator.
> B.[KEY] = e.EmployeeID
> For the OR operator, you will need to add the DISTINCT operator to prevent
> dups in your results.
> Hope that helps!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
> news:F5A5A066-BE17-43CC-83E3-E65E6270AC99@.microsoft.com...
>
>
|||Jeff wrote on Mon, 19 Sep 2005 11:19:06 -0700:
> Thanks for the reference John.
> I read it over well and tried the examples which I've somewhat tried
> before.
> Aparently when using the [and] operator with contains[table], it looks for
> both "words" to exist in the same column, and not scross columns.
> I think perhaps the FreeTextable will suit our needs, it's just a shame
> that it does not support wildcards yet.
You can achieve your result using Contains, but it requires more processing:
select * from mytable where contains(*,'"pin*"') and contains(*,'"yellow*"')
This will find all rows with pin* in the FTS index, and all rows with
yello*, and then combine them together to find all rows that have both terms
in either column.
However, from a performance point of view this may not be satisfactory as
there is an FTS search for each word rather than all at once. In my own
system I maintain an extra column that is a contenation of the 6 columns
that I use in my searches - so if I want a search just on book titles I
would use Contains(title,@.words) but if I wanted the words to appear in any
columns then I'd use Contains(keywords,@.words). It does mean that when data
is changed the keywords field needs to be rebuilt, but all data is processed
from files via an in-house application so this is done automatically.
Additional storage is required for the extra column, but I'd rather use up a
bit more of my disk space than have slow FTS results (disk space is after
all relatively cheap compared to the perceived performance of a web site
search by customers).
Dan
Sunday, February 12, 2012
Constraint question
I need to put a constraint on a table but am unsure how to proceed. Consider
the following fields:
AcctNo - varchar(10), indexed, duplicates OK
Status - varchar(2)
The logical rules I need to enforce are such that if any records exist with
duplicate values in the AcctNo field, only one record can have an 'active'
status, where active status is defined as any status other than 'X'.
My question is whether a check constraint is the correct tool to enforce
this, and if so how would I create one that can enforce rules based on
multiple fields and multiple records?
Sample data would look something like this:
AcctNo Status
-- --
123456 RO
123456 X
987654 C
135790 RO
135790 X
777777 C
999999 RO
888888 RO
888888 Xcreate table t(i int identity(1,1),
AcctNo varchar(10),
Status varchar(2),
i_when_inactive as (case when status='X' then i else null end))
create unique index unique_active_acctno on t(AcctNo, i_when_inactive)
insert into t(acctno, status) values('ABC', 'C')
-- duplicate inactive rows
insert into t(acctno, status) values('ABC', 'X')
insert into t(acctno, status) values('ABC', 'X')
insert into t(acctno, status) values('ABC', 'X')
-- can't insert active duplicates
insert into t(acctno, status) values('ABC', 'C')
select * from t
drop table t|||Thanks AK. Works great.
"AK" wrote:
> create table t(i int identity(1,1),
> AcctNo varchar(10),
> Status varchar(2),
> i_when_inactive as (case when status='X' then i else null end))
> create unique index unique_active_acctno on t(AcctNo, i_when_inactive)
> insert into t(acctno, status) values('ABC', 'C')
> -- duplicate inactive rows
> insert into t(acctno, status) values('ABC', 'X')
> insert into t(acctno, status) values('ABC', 'X')
> insert into t(acctno, status) values('ABC', 'X')
> -- can't insert active duplicates
> insert into t(acctno, status) values('ABC', 'C')
> select * from t
> drop table t
>|||>> My question is whether a check constraint is the correct tool to enforce this, and
if so how would I create one that can enforce rules based on multiple fields [sic] and
multiple records [sic]? <<
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. Also, account numbers and most
codes are fixed length.
If you want to get the current row in a history, then write it like
this:
CREATE TABLE AccountHistory
(acct_nbr CHAR(10) NOT NULL,
acct_status CHAR(2) NOT NULL,
start_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_date DATETIME, -- null means current
CHECK (start_date < end_date),
PRIMARY KEY (acct_nbr, start_date),
etc.);
Now create a VIEW with the current row, something like this;
CREATE VIEW Accounts (acct_nbr, acct_status, start_date, ..)
AS
SELECT acct_nbr, acct_status, start_date, ..
FROM AccountHistory
WHERE end_date IS NULL;
To be sure that you have only one currently active account status, you
can use a trigger or some very horrible proprietary code. However, if
you had SQL-92, you could use:
CHECK(1 = ALL (SELECT COUNT(*)
FROM AccountHistory
WHERE end_date IS NULL
GROUP BY acct_nbr))|||Thanks for the DBMS lesson perfessor. The other guy's real world solution
works great.
"--CELKO--" wrote:
> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files. Also, account numbers and most
> codes are fixed length.
> If you want to get the current row in a history, then write it like
> this:
> CREATE TABLE AccountHistory
> (acct_nbr CHAR(10) NOT NULL,
> acct_status CHAR(2) NOT NULL,
> start_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
> end_date DATETIME, -- null means current
> CHECK (start_date < end_date),
> PRIMARY KEY (acct_nbr, start_date),
> etc.);
> Now create a VIEW with the current row, something like this;
> CREATE VIEW Accounts (acct_nbr, acct_status, start_date, ..)
> AS
> SELECT acct_nbr, acct_status, start_date, ..
> FROM AccountHistory
> WHERE end_date IS NULL;
> To be sure that you have only one currently active account status, you
> can use a trigger or some very horrible proprietary code. However, if
> you had SQL-92, you could use:
> CHECK(1 = ALL (SELECT COUNT(*)
> FROM AccountHistory
> WHERE end_date IS NULL
> GROUP BY acct_nbr))
>
the following fields:
AcctNo - varchar(10), indexed, duplicates OK
Status - varchar(2)
The logical rules I need to enforce are such that if any records exist with
duplicate values in the AcctNo field, only one record can have an 'active'
status, where active status is defined as any status other than 'X'.
My question is whether a check constraint is the correct tool to enforce
this, and if so how would I create one that can enforce rules based on
multiple fields and multiple records?
Sample data would look something like this:
AcctNo Status
-- --
123456 RO
123456 X
987654 C
135790 RO
135790 X
777777 C
999999 RO
888888 RO
888888 Xcreate table t(i int identity(1,1),
AcctNo varchar(10),
Status varchar(2),
i_when_inactive as (case when status='X' then i else null end))
create unique index unique_active_acctno on t(AcctNo, i_when_inactive)
insert into t(acctno, status) values('ABC', 'C')
-- duplicate inactive rows
insert into t(acctno, status) values('ABC', 'X')
insert into t(acctno, status) values('ABC', 'X')
insert into t(acctno, status) values('ABC', 'X')
-- can't insert active duplicates
insert into t(acctno, status) values('ABC', 'C')
select * from t
drop table t|||Thanks AK. Works great.
"AK" wrote:
> create table t(i int identity(1,1),
> AcctNo varchar(10),
> Status varchar(2),
> i_when_inactive as (case when status='X' then i else null end))
> create unique index unique_active_acctno on t(AcctNo, i_when_inactive)
> insert into t(acctno, status) values('ABC', 'C')
> -- duplicate inactive rows
> insert into t(acctno, status) values('ABC', 'X')
> insert into t(acctno, status) values('ABC', 'X')
> insert into t(acctno, status) values('ABC', 'X')
> -- can't insert active duplicates
> insert into t(acctno, status) values('ABC', 'C')
> select * from t
> drop table t
>|||>> My question is whether a check constraint is the correct tool to enforce this, and
if so how would I create one that can enforce rules based on multiple fields [sic] and
multiple records [sic]? <<
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. Also, account numbers and most
codes are fixed length.
If you want to get the current row in a history, then write it like
this:
CREATE TABLE AccountHistory
(acct_nbr CHAR(10) NOT NULL,
acct_status CHAR(2) NOT NULL,
start_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_date DATETIME, -- null means current
CHECK (start_date < end_date),
PRIMARY KEY (acct_nbr, start_date),
etc.);
Now create a VIEW with the current row, something like this;
CREATE VIEW Accounts (acct_nbr, acct_status, start_date, ..)
AS
SELECT acct_nbr, acct_status, start_date, ..
FROM AccountHistory
WHERE end_date IS NULL;
To be sure that you have only one currently active account status, you
can use a trigger or some very horrible proprietary code. However, if
you had SQL-92, you could use:
CHECK(1 = ALL (SELECT COUNT(*)
FROM AccountHistory
WHERE end_date IS NULL
GROUP BY acct_nbr))|||Thanks for the DBMS lesson perfessor. The other guy's real world solution
works great.
"--CELKO--" wrote:
> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files. Also, account numbers and most
> codes are fixed length.
> If you want to get the current row in a history, then write it like
> this:
> CREATE TABLE AccountHistory
> (acct_nbr CHAR(10) NOT NULL,
> acct_status CHAR(2) NOT NULL,
> start_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
> end_date DATETIME, -- null means current
> CHECK (start_date < end_date),
> PRIMARY KEY (acct_nbr, start_date),
> etc.);
> Now create a VIEW with the current row, something like this;
> CREATE VIEW Accounts (acct_nbr, acct_status, start_date, ..)
> AS
> SELECT acct_nbr, acct_status, start_date, ..
> FROM AccountHistory
> WHERE end_date IS NULL;
> To be sure that you have only one currently active account status, you
> can use a trigger or some very horrible proprietary code. However, if
> you had SQL-92, you could use:
> CHECK(1 = ALL (SELECT COUNT(*)
> FROM AccountHistory
> WHERE end_date IS NULL
> GROUP BY acct_nbr))
>
Subscribe to:
Posts (Atom)