sql server full text search: Hi,
I am new to Transact SQL and am trying to concoct a full text search across
multiple columns of a single table, and then replace the found text with a
new string.
Some of these ntext columns have multiple strings separated by 'special
characters' (i.e. carriage return).
This seems like a common UPDATE TABLE requirement to me...
What is the proper syntax?
Thx,
Barry

This is actually quite difficult. There are two problems, the first is how
do you know which column the "hit" is found in.

one option is the brute force method:

update tableName set col1=replace(col1,'"My Search Phrase"','"My Modified
Phrase"'), set col2=replace(col2,'"My Search Phrase"','"My Modified
Phrase"')

Update the entire table. This is safe as the phrases will only be replaced
if they are found.

You could do something like this as well which will restrict the row
modifications to only the ones where a hit exists.

update twocolumntable
set charcol1=replace(charcol1,'hilary','hillary'),
charcol2=replace(charcol2,'hilary','hillary')
from twocolumntable
left join (select [key] from
containstable(twocolumntable,charcol1,'Hilary')) as k on
k.[key]=twocolumntable.pk
left join (select [key] from
containstable(twocolumntable,charcol2,'Hilary')) as l on
l.[key]=twocolumntable.pk
where l.[key] is not null or k.[key] is not null

The real problem is dealing with the line/page feed marks. If you want to
treat a phrase as a block you would need to account for all possibilities of
where these tags might occur.

For example to replace Barry Prentiss in all its permutations and
combinations you would have to replace

Barry Char(10) Char(13) Prentiss, Barry Char(13) Char(10) Prentiss, and this
is just for carraige return line feed. For a three word phrase it would get
even more complicated.

If you can whack the formatting tags it would be easier.

--
Hilary Cotter

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



[quoted text, click to view]