«

I need to match a string in a database, but ignore any trailing punctuation such as a
period, exclamation point, or question mark.

for example,

SELECT * FROM tbl_stuff WHERE phrase = term

So, if row 5 of the database contains phrase="This is great and exciting!"
and term = "This is great and exciting" (no trailing exclamation point),
it will match row 5.

But if term = "This is great", it will not match.

Can anyone please help?

Thanks!!!

Toni wrote:
> I need to match a string in a database, but ignore any trailing
> punctuation such as a period, exclamation point, or question mark.
>
> for example,
>
> SELECT * FROM tbl_stuff WHERE phrase = term
>
> So, if row 5 of the database contains phrase="This is great and
> exciting!" and term = "This is great and exciting" (no trailing
> exclamation
> point), it will match row 5.
>
> But if term = "This is great", it will not match.
>
> Can anyone please help?
>
> Thanks!!!

You will have to use LIKE and append a single-character wildcard to the end
of the search string.

WHERE phrase like 'This is great and exciting_'

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Gazing into my crystal ball I observed "Toni" <> writing
in news:#:

> I need to match a string in a database, but ignore any trailing
> punctuation such as a period, exclamation point, or question mark.
>
> for example,
>
> SELECT * FROM tbl_stuff WHERE phrase = term
>
> So, if row 5 of the database contains phrase="This is great and
> exciting!" and term = "This is great and exciting" (no trailing
> exclamation point), it will match row 5.
>
> But if term = "This is great", it will not match.
>
> Can anyone please help?
>
> Thanks!!!
>
>
>

Two things:
1. It's not a good idea to SELECT * - you should always explicitly state
the columns you need in a query. You can Google for the reasons why.
2. What you need is LIKE:
SELECT fields FROM TABLE WHERE field LIKE 'This is great and exciting%'
The percent sign will catch anything where "This is a great and
exciting" is at the front of the phrase.


--
Adrienne Boswell at Home
Arbpen Web Site Design Services
http://www.cavalcade-of-coding.info
Please respond to the group so others can share

"Bob Barrows" <> wrote in message
news:...
> Toni wrote:
>> I need to match a string in a database, but ignore any trailing
>> punctuation such as a period, exclamation point, or question mark.
>>
>> for example,
>>
>> SELECT * FROM tbl_stuff WHERE phrase = term
>>
>> So, if row 5 of the database contains phrase="This is great and
>> exciting!" and term = "This is great and exciting" (no trailing exclamation
>> point), it will match row 5.
>>
>> But if term = "This is great", it will not match.
>>
>> Can anyone please help?
>>
>> Thanks!!!
>
> You will have to use LIKE and append a single-character wildcard to the end of the
> search string.
>
> WHERE phrase like 'This is great and exciting_'

Does the single-character widlcard match zero or one char?
Also, what if the trailing char is not punctuation, as in

House
Houses
Houser

....etc...?

"Adrienne Boswell" <> wrote in message
news:Xns9C71BEC731F87arbpenyahoocom@188.40.43.213. ..
> Gazing into my crystal ball I observed "Toni" <> writing
> in news:#:
>
>> I need to match a string in a database, but ignore any trailing
>> punctuation such as a period, exclamation point, or question mark.
>>
>> for example,
>>
>> SELECT * FROM tbl_stuff WHERE phrase = term
>>
>> So, if row 5 of the database contains phrase="This is great and
>> exciting!" and term = "This is great and exciting" (no trailing
>> exclamation point), it will match row 5.
>>
>> But if term = "This is great", it will not match.
>>
>> Can anyone please help?
>>
>> Thanks!!!
>>
>>
>>
>
:
> 2. What you need is LIKE:
> SELECT fields FROM TABLE WHERE field LIKE 'This is great and exciting%'
> The percent sign will catch anything where "This is a great and
> exciting" is at the front of the phrase.

But what if the database contains
4 - This is Great
5 - This is Great and Exciting

Searching for "This is great%" will match both fields, right?

Toni wrote:
> "Bob Barrows" <> wrote in message
> news:...
>> Toni wrote:
>>> I need to match a string in a database, but ignore any trailing
>>> punctuation such as a period, exclamation point, or question mark.
>>>
>>> for example,
>>>
>>> SELECT * FROM tbl_stuff WHERE phrase = term
>>>
>>> So, if row 5 of the database contains phrase="This is great and
>>> exciting!" and term = "This is great and exciting" (no trailing
>>> exclamation point), it will match row 5.
>>>
>>> But if term = "This is great", it will not match.
>>>
>>> Can anyone please help?
>>>
>>> Thanks!!!
>>
>> You will have to use LIKE and append a single-character wildcard to
>> the end of the search string.
>>
>> WHERE phrase like 'This is great and exciting_'
>
> Does the single-character widlcard match zero or one char?

Oh! My bad ... one character. There are workarounds that depend on what
database you are using.

> Also, what if the trailing char is not punctuation, as in
>
> House
> Houses
> Houser

any character. All of these will match.
You can do limited pattern-matching, but the database you are using
imposes limits on the type of patterns that can be matched. I won't try
to go into detail until you confirm what database type and version you
are using.


--
HTH,
Bob Barrows

"Bob Barrows" <> wrote in message
news:%...
> Toni wrote:
>> "Bob Barrows" <> wrote in message
>> news:...
>>> Toni wrote:
>>>> I need to match a string in a database, but ignore any trailing
>>>> punctuation such as a period, exclamation point, or question mark.
>>>>
>>>> for example,
>>>>
>>>> SELECT * FROM tbl_stuff WHERE phrase = term
>>>>
>>>> So, if row 5 of the database contains phrase="This is great and
>>>> exciting!" and term = "This is great and exciting" (no trailing
>>>> exclamation point), it will match row 5.
>>>>
>>>> But if term = "This is great", it will not match.
>>>>
>>>> Can anyone please help?
>>>>
>>>> Thanks!!!
>>>
>>> You will have to use LIKE and append a single-character wildcard to
>>> the end of the search string.
>>>
>>> WHERE phrase like 'This is great and exciting_'
>>
>> Does the single-character widlcard match zero or one char?
>
> Oh! My bad ... one character. There are workarounds that depend on what
> database you are using.
>
>> Also, what if the trailing char is not punctuation, as in
>>
>> House
>> Houses
>> Houser
>
> any character. All of these will match.
> You can do limited pattern-matching, but the database you are using
> imposes limits on the type of patterns that can be matched. I won't try
> to go into detail until you confirm what database type and version you
> are using.
>
>
> --
> HTH,
> Bob Barrows

Bob, my hosting plan has me on MS SQL 2005. However, I do have the option to upgrade to
MS SQL 2008.

Toni wrote:
>> any character. All of these will match.
>> You can do limited pattern-matching, but the database you are using
>> imposes limits on the type of patterns that can be matched. I won't
>> try to go into detail until you confirm what database type and version
>> you are using.
>>
>>
> Bob, my hosting plan has me on MS SQL 2005. However, I do have the
> option to upgrade to MS SQL 2008.

I could have sworn I replied to this but I don't see my reply here. What I
said was:
with SQL Server, you can do something like this:

WHERE phrase=term or phrase like term + '[.,!,?]'

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Toni wrote:
> "Bob Barrows" <> wrote in message
> news:%...
>> Toni wrote:
>>> Also, what if the trailing char is not punctuation, as in
>>>
>>> House
>>> Houses
>>> Houser
>>
>> any character. All of these will match.
>> You can do limited pattern-matching, but the database you are using
>> imposes limits on the type of patterns that can be matched. I won't
>> try to go into detail until you confirm what database type and
>> version you are using.
>>
>>
>> --
>> HTH,
>> Bob Barrows
>
> Bob, my hosting plan has me on MS SQL 2005. However, I do have the
> option to upgrade to MS SQL 2008.

With SQL2005, you can do something like this:

WHERE phrase = term OR phrase LIKE term + '[.,!,?]'

Look up LIKE in SQL BOL (Books OnLine)

--
HTH,
Bob Barrows

"Bob Barrows" wrote...
>
> With SQL2005, you can do something like this:
>
> WHERE phrase = term OR phrase LIKE term + '[.,!,?]'

Ah! So, if I put the terms in [brackets], it treats it like a RegEx "or"!

That should do it, thanks Bob!