Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > SQL Statement WHERE ignores trailing punctuation

Reply
Thread Tools

SQL Statement WHERE ignores trailing punctuation

 
 
Toni
Guest
Posts: n/a
 
      08-24-2009
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!!!


 
Reply With Quote
 
 
 
 
Bob Barrows
Guest
Posts: n/a
 
      08-25-2009
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"


 
Reply With Quote
 
 
 
 
Adrienne Boswell
Guest
Posts: n/a
 
      08-25-2009
Gazing into my crystal ball I observed "Toni" <(E-Mail Removed)> writing
in news:#(E-Mail Removed):

> 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

 
Reply With Quote
 
Toni
Guest
Posts: n/a
 
      08-25-2009

"Bob Barrows" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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...?



 
Reply With Quote
 
Toni
Guest
Posts: n/a
 
      08-25-2009

"Adrienne Boswell" <(E-Mail Removed)> wrote in message
news:Xns9C71BEC731F87arbpenyahoocom@188.40.43.213. ..
> Gazing into my crystal ball I observed "Toni" <(E-Mail Removed)> writing
> in news:#(E-Mail Removed):
>
>> 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?








 
Reply With Quote
 
Bob Barrows
Guest
Posts: n/a
 
      08-25-2009
Toni wrote:
> "Bob Barrows" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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


 
Reply With Quote
 
Toni
Guest
Posts: n/a
 
      08-25-2009

"Bob Barrows" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Toni wrote:
>> "Bob Barrows" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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.



 
Reply With Quote
 
Bob Barrows
Guest
Posts: n/a
 
      08-25-2009
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"


 
Reply With Quote
 
Bob Barrows
Guest
Posts: n/a
 
      08-26-2009
Toni wrote:
> "Bob Barrows" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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


 
Reply With Quote
 
Toni
Guest
Posts: n/a
 
      08-26-2009
"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!





 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Trailing commas when entering data into SQL using ASP Techhead ASP General 3 02-09-2009 09:33 PM
asp and sql statement in sql server db weiwei ASP General 3 09-22-2004 04:12 PM
DBI SQL column datatype not jiving with SQL statement requirement dna Perl 1 01-18-2004 04:15 PM
Re: SQL statement working in SQL Server but not in .aspx.cs page David Browne ASP .Net 0 08-21-2003 10:43 PM
Re: SQL statement working in SQL Server but not in .aspx.cs page William \(Bill\) Vaughn ASP .Net 0 08-21-2003 10:41 PM



Advertisments