Convert Punctuation to Spaces?

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...Q_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...Q_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!!