Hi,

I have a table of text. I need to search for whole words within this text...
For example, I need to be able to search for records that contain 'dog' but
not return 'hotdog' or 'dogma' for example.

I am doing this by throwing a space around both the records in the table and
the search word like this:
WHERE (' ' + Text + ' ') Like ('% ' + Search + ' %')

The problem is that punctuation needs to be stripped out of the text so that
it will still find "...walking the dog."

Is there a way to update, converting a certain set of characters into
another character (i.e. a space) and/or to do the same thing during the word
search query itself?

Thanks!

"HumanJHawkins" <JHawkins@HumanitiesSoftware.Com> wrote in message
news:rlmbc.13192$lt2.8227@newsread1.news.pas.earth link.net...
> Hi,
>
> I have a table of text. I need to search for whole words within this
text...
> For example, I need to be able to search for records that contain 'dog'
but
> not return 'hotdog' or 'dogma' for example.
>
> I am doing this by throwing a space around both the records in the table
and
> the search word like this:
> WHERE (' ' + Text + ' ') Like ('% ' + Search + ' %')
>
> The problem is that punctuation needs to be stripped out of the text so
that
> it will still find "...walking the dog."
>
> Is there a way to update, converting a certain set of characters into
> another character (i.e. a space) and/or to do the same thing during the
word
> search query itself?
>
> Thanks!
>
>

Assuming you have MSSQL 2000, you could write a UDF to remove all
punctuation characters from a string, but then you'd end up with this:

WHERE dbo.fn_RemovePunc(MyColumn) LIKE '% ' + @SearchString + ' % '

That will probably cause a performance issue, because the UDF will be
invoked once per row during queries, although you could create a computed
column using the UDF and index it.

However, perhaps a better solution here would be to look at using full-text
indexing? The CONTAINS() predicate can do what you need, and is much more
powerful than LIKE.

Simon

"Simon Hayes" <sql@hayes.ch> wrote in message
news:406e7002$1_1@news.bluewin.ch...
> "HumanJHawkins" <JHawkins@HumanitiesSoftware.Com> wrote in message
> news:rlmbc.13192$lt2.8227@newsread1.news.pas.earth link.net...
>> <CUT>For example, I need to be able to search for records that contain
'dog'
>> but not return 'hotdog' or 'dogma' for example.
>> <CUT>

> The CONTAINS() predicate can do what you need, and is much more
> powerful than LIKE.

That helped tons. I got the basic "CONTAINS" predicate to work, but do not
get any results when I add "FORMSOF" into the mix. Do you see the problem
with the following?

WHERE CONTAINS (vchContentText , ' FORMSOF (INFLECTIONAL,
@SearchIncludes) ')

All of the examples I found seemed to have a space and single quotes around
the whole "FORMSOF" bit, though it didn't seem to matter whether I removed
the space or the single quotes.

Thanks!

"HumanJHawkins" <JHawkins@HumanitiesSoftware.Com> wrote in message
news:dtjcc.16960$lt2.8344@newsread1.news.pas.earth link.net...
>
> "Simon Hayes" <sql@hayes.ch> wrote in message
> news:406e7002$1_1@news.bluewin.ch...
> > "HumanJHawkins" <JHawkins@HumanitiesSoftware.Com> wrote in message
> > news:rlmbc.13192$lt2.8227@newsread1.news.pas.earth link.net...
> >> <CUT>For example, I need to be able to search for records that contain
> 'dog'
> >> but not return 'hotdog' or 'dogma' for example.
> >> <CUT>
>
> > The CONTAINS() predicate can do what you need, and is much more
> > powerful than LIKE.
>
> That helped tons. I got the basic "CONTAINS" predicate to work, but do not
> get any results when I add "FORMSOF" into the mix. Do you see the problem
> with the following?
>
> WHERE CONTAINS (vchContentText , ' FORMSOF (INFLECTIONAL,
> @SearchIncludes) ')
>
> All of the examples I found seemed to have a space and single quotes
around
> the whole "FORMSOF" bit, though it didn't seem to matter whether I removed
> the space or the single quotes.
>
> Thanks!
>
>

This may help:

http://oldlook.experts-exchange.com:..._20711909.html

Fulltext is quite a specialized area, and it seems to have a number of
quirks, so you may want to consider posting questions in
microsoft.public.sqlserver.fulltext - you'll probably get a better response.

Simon

> This may help:
>
>
http://oldlook.experts-exchange.com:..._20711909.html
>
> Fulltext is quite a specialized area, and it seems to have a number of
> quirks, so you may want to consider posting questions in
> microsoft.public.sqlserver.fulltext - you'll probably get a better
response.

Thanks!

>"HumanJHawkins" <JHawkins@HumanitiesSoftware.Com> wrote in message
> news:rlmbc.13192$lt2.8227@newsread1.news.pas.earth link.net...
>><CUT>I need to be able to search for records that contain 'dog'
>> but not return 'hotdog' or 'dogma' for example.
>> <CUT>

"Simon Hayes" <sql@hayes.ch> replied in message
news:406e7002$1_1@news.bluewin.ch...
>perhaps a better solution here would be to look at using full-text
> indexing? The CONTAINS() predicate can do what you need, and is much more
> powerful than LIKE.

Thanks Simon. The syntax needed is:

In SQL:
-- In the declarations or parameters:
@Variable varchar(256) = 'FORMSOF(INFLECTIONAL,"word")'

-- Then, in the WHERE clause:
CONTAINS (TableName, @Variable)

If passing the string from VB to a stored procedure, prepare the string in
VB with:
TheVariable= "'FORMSOF(INFLECTIONAL,""" & TheVariable & """)'"

Cheers!!