SQL Statement WHERE ignores trailing punctuation

Discussion in 'ASP General' started by Toni, Aug 25, 2009.

  1. Toni

    Toni Guest

    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, Aug 25, 2009
    #1
    1. Advertising

  2. Toni

    Bob Barrows Guest

    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"
    Bob Barrows, Aug 25, 2009
    #2
    1. Advertising

  3. 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
    Adrienne Boswell, Aug 25, 2009
    #3
  4. Toni

    Toni Guest

    "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...?
    Toni, Aug 25, 2009
    #4
  5. Toni

    Toni Guest

    "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, Aug 25, 2009
    #5
  6. Toni

    Bob Barrows Guest

    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, Aug 25, 2009
    #6
  7. Toni

    Toni Guest

    "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, Aug 25, 2009
    #7
  8. Toni

    Bob Barrows Guest

    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"
    Bob Barrows, Aug 25, 2009
    #8
  9. Toni

    Bob Barrows Guest

    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, Aug 26, 2009
    #9
  10. Toni

    Toni Guest

    "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!
    Toni, Aug 26, 2009
    #10
  11. Toni

    Bob Barrows Guest

    Toni wrote:
    > "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"!


    Yes, you can read more about it in SQL BOL. If you don't have BOL
    installed on your machine, look here:
    http://www.aspfaq.com/show.asp?id=2229

    >
    > That should do it, thanks Bob!


    You're welcome. :)
    --
    HTH,
    Bob Barrows
    Bob Barrows, Aug 26, 2009
    #11
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Chris Leffer

    Regular expression for punctuation

    Chris Leffer, Jul 9, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    414
    Chris Leffer
    Jul 9, 2003
  2. Chris R. Timmons

    Re: Regular expression for punctuation

    Chris R. Timmons, Jul 10, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    471
    Chris R. Timmons
    Jul 10, 2003
  3. DBLWizard
    Replies:
    10
    Views:
    735
    Brock Allen
    Apr 2, 2005
  4. dew

    Stripping out punctuation marks

    dew, Feb 6, 2006, in forum: ASP .Net
    Replies:
    1
    Views:
    418
    Nathan Sokalski
    Feb 7, 2006
  5. Techhead
    Replies:
    3
    Views:
    222
    Bob Barrows
    Feb 9, 2009
Loading...

Share This Page