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