Convert Punctuation to Spaces?

[HumanJHawkins]

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!

[Simon Hayes]

"HumanJHawk ins" <JH******@Human itiesSoftware.C om> wrote in message
news:rl******** **********@news read1.news.pas. earthlink.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_RemovePu nc(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

[HumanJHawkins]

"Simon Hayes" <sq*@hayes.ch > wrote in message
news:40******** **@news.bluewin .ch...

"HumanJHawk ins" <JH******@Human itiesSoftware.C om> wrote in message
news:rl******** **********@news read1.news.pas. earthlink.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!

[Simon Hayes]

"HumanJHawk ins" <JH******@Human itiesSoftware.C om> wrote in message
news:dt******** **********@news read1.news.pas. earthlink.net.. .

"Simon Hayes" <sq*@hayes.ch > wrote in message
news:40******** **@news.bluewin .ch...

"HumanJHawk ins" <JH******@Human itiesSoftware.C om> wrote in message
news:rl******** **********@news read1.news.pas. earthlink.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.publi c.sqlserver.ful ltext - you'll probably get a better response.

Simon

[HumanJHawkins]

> 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.publi c.sqlserver.ful ltext - you'll probably get a better

response.

Thanks!

[HumanJHawkins]

>"HumanJHawkins " <JH******@Human itiesSoftware.C om> wrote in message

news:rl******** **********@news read1.news.pas. earthlink.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" <sq*@hayes.ch > replied in message
news:40******** **@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(INFLEC TIONAL,"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(INFLE CTIONAL,""" & TheVariable & """)'"

Cheers!!