Multi Column full-text search question

I would like to select rows containing some text which is more than one word.
The problem is that the text can appear in different colums, meaning, one
word in this column and another in that. moreove, I need to select only the
rows containing all the words.
The fulltext search is on one table only (multi columns)
The text size in words is variable
Can someone help?
Thanks in advance
This is a difficult question. Basically Contains will only see the words in
your search phrase if they occur in the same column, no matter what type of
search you are doing; single column, multiple column, or all columns.
For FreeText the hit will come if the word or one of the generations of the
word occurs in any of the columns.
create database multicolumn
go
use multicolumn
go
sp_fulltext_database 'enable'
go
create table fulltexttable
(pk int not null identity constraint fulltexttablepk primary key,
charcol1 char(30),
charcol2 char(30),
charcol3 char(30))
GO
insert into fulltexttable(charcol1, charcol2) values('james','bond')
insert into fulltexttable(charcol1, charcol2) values('james bond','mike')
GO
create fulltext catalog test as default
GO
create fulltext index on fulltexttable(charcol1, charcol2)
key index fulltexttablepk
GO
select * from containstable(fulltexttable,*,'"james bond"') -- 1 row, 2nd
one
select * from containstable(fulltexttable,charcol1,'"james bond"') -- 1 row,
2nd one
select * from containstable(fulltexttable,(charcol1,charcol2),'" james
bond"') -- 1 row, 2nd one
select * from freetexttable(fulltexttable,*,'james bond') -- 2 rows, 1 and 2
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"dedi" <dedi@.discussions.microsoft.com> wrote in message
news:CFE85410-5AFF-4F84-B376-3E864DFDB623@.microsoft.com...
>I would like to select rows containing some text which is more than one
>word.
> The problem is that the text can appear in different colums, meaning, one
> word in this column and another in that. moreove, I need to select only
> the
> rows containing all the words.
> The fulltext search is on one table only (multi columns)
> The text size in words is variable
> Can someone help?
> Thanks in advance
sql

Multi column

Hi,
I dont know how to word this but I'll try. I have a report that shows two
field in the output, i.e city and count. When the report is run it displays
about 44 rows on one page, but since there are only two fields the rest of
the page is left blank on the right hand side which can easily accomodate two
more column of same fields of city and count. For example it prints row 1
thru 44 on one page and 45 thru ... I would like it to print the 45 thru ...
on the right side of the page which is blank. I'm pretty sure this can be
done.
Please help!while in Layout tab in Report Designer, go to Report then Report Properties.
Click the Layout tab and play around with the number in Columns box.
"Shan" <Shan@.discussions.microsoft.com> wrote in message
news:D65B284A-2C06-4D3E-BC1A-BBF03DD51679@.microsoft.com...
> Hi,
> I dont know how to word this but I'll try. I have a report that shows two
> field in the output, i.e city and count. When the report is run it
> displays
> about 44 rows on one page, but since there are only two fields the rest of
> the page is left blank on the right hand side which can easily accomodate
> two
> more column of same fields of city and count. For example it prints row 1
> thru 44 on one page and 45 thru ... I would like it to print the 45 thru
> ...
> on the right side of the page which is blank. I'm pretty sure this can be
> done.
> Please help!|||I changed the column from 1 to 2 but it still wont move over to the second
column. Any idea?
Thanks
"ME" wrote:
> while in Layout tab in Report Designer, go to Report then Report Properties.
> Click the Layout tab and play around with the number in Columns box.
>
> "Shan" <Shan@.discussions.microsoft.com> wrote in message
> news:D65B284A-2C06-4D3E-BC1A-BBF03DD51679@.microsoft.com...
> > Hi,
> >
> > I dont know how to word this but I'll try. I have a report that shows two
> > field in the output, i.e city and count. When the report is run it
> > displays
> > about 44 rows on one page, but since there are only two fields the rest of
> > the page is left blank on the right hand side which can easily accomodate
> > two
> > more column of same fields of city and count. For example it prints row 1
> > thru 44 on one page and 45 thru ... I would like it to print the 45 thru
> > ...
> > on the right side of the page which is blank. I'm pretty sure this can be
> > done.
> >
> > Please help!
>
>

MSWord smart quotes and SQL Server

We use Lyris Listmanager to send out bulk email - the product uses SQL
server. If someone cuts and pastes into the email product from Word, and
there are smart quotes in the document, they show up as ? marks on our
website. The emails go out fine, and IE displays smart quotes if I don't ge
t
them from SQL, so I am assuming the problem is with SQL. Has anyone had thi
s
problem and if so, please tell me how you corrected it. We tried adding cod
e
to our pages to find the ascii codes for smart quotes and it doesn't find
them.I haven't seen it, but I would suggest just using replace to get rid of the
wacky quotes:
replace (column,'"','"')
The smart quotes look good in a document, but when I wrote my book, they
turned code sample quotes to smart quotes so much it drove me banannas.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"yorkie" <yorkie@.discussions.microsoft.com> wrote in message
news:F6B9B990-D4B6-4FC8-BD71-D970E737AEBA@.microsoft.com...
> We use Lyris Listmanager to send out bulk email - the product uses SQL
> server. If someone cuts and pastes into the email product from Word, and
> there are smart quotes in the document, they show up as ? marks on our
> website. The emails go out fine, and IE displays smart quotes if I don't
> get
> them from SQL, so I am assuming the problem is with SQL. Has anyone had
> this
> problem and if so, please tell me how you corrected it. We tried adding
> code
> to our pages to find the ascii codes for smart quotes and it doesn't find
> them.|||Thanks, we actually tried that and were not successful, it messed up the
field and the newsletter would not display at all. Appreciate the advise th
o!
"Louis Davidson" wrote:

> I haven't seen it, but I would suggest just using replace to get rid of th
e
> wacky quotes:
> replace (column,'"','"')
> The smart quotes look good in a document, but when I wrote my book, they
> turned code sample quotes to smart quotes so much it drove me banannas.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often convincing.
"
> (Oscar Wilde)
> "yorkie" <yorkie@.discussions.microsoft.com> wrote in message
> news:F6B9B990-D4B6-4FC8-BD71-D970E737AEBA@.microsoft.com...
>
>|||It shouldn't mess up the data in the output, unless there is other wierdness
going on. Could it have been that the double quotes were then the problem?
I think you have to use an escape character of some sort, I think it is:
"
So try:
replace (column,'"','"')
I am not a web programmer, so I am just guessing about this part, but it is
a possibility.
If this doesn't work, can you post an example? one thing to try is to use
ascii ('?') where the ? represents the data you are trying to replace, if
that is the problem. The smart quotes in the web page may be some other
escaped sequence of characters too.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"yorkie" <yorkie@.discussions.microsoft.com> wrote in message
news:781E9DC1-53B5-42F2-9B42-DD8E9BF8AED5@.microsoft.com...
> Thanks, we actually tried that and were not successful, it messed up the
> field and the newsletter would not display at all. Appreciate the advise
> tho!
> "Louis Davidson" wrote:
>sql