Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    39

    Unanswered: Searching for whole words?

    How can you search for the occurance of a whole word in a string? but not return any results that have the word as a substring.

    For instance, if I search for the term 'scene' in a column. Then it will only return rows that have the word 'scene' and not those with the word 'scenery'. I've tried the following sql, but it relies on having text either side of the word as well. If the word 'scene' is on the begining or end of the cell then it is not returned.

    Code:
    SELECT Name, Description
    FROM tblWine 
    WHERE Name LIKE '%[^a-zA-Z]scene[^a-zA-Z]%' 
    OR Description LIKE '%[^a-zA-Z]scene[^a-zA-Z]%'
    Any ideas?

    Goran

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Hmmm..try this:
    where ' ' + Name + ' ' like '% scene %'
    Punction would require more tweaking...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2003
    Location
    Pittsburgh, PA
    Posts
    86

    Try this

    SELECT Name, Description
    FROM tblWine
    WHERE Name LIKE '% scene %'
    OR Description LIKE '% scene %'
    OR Name LIKE 'scene %'
    OR Name LIKE '% scene'
    OR Name LIKE '% scene.'
    OR Name LIKE '% scene,'
    OR Description LIKE 'scene %'
    OR Description LIKE '% scene'
    OR Description LIKE '% scene.'
    OR Description LIKE '% scene,'

    That would find you all occurences of the word 'scene' that were in the middle of a phrase, beginning of a phrase, or end of a phrase (if the phrase ended with a period, comma, or no punctuation).

    However, if you're trying to do this dynamically, where you'll be switching out the word 'scene' with other words, you've got a lot more work to do.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It hurts me to even type this, but you'll be doing a table scan anyway so it won't hurt any more than that...
    Code:
    SELECT Name, Description
       FROM tblWine 
       WHERE '.' + Name + '.' LIKE '%[^a-zA-Z]scene[^a-zA-Z]%' 
          OR  '.' + Description + '.' LIKE '%[^a-zA-Z]scene[^a-zA-Z]%'
    -PatP

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you could temporarily dump your table into a MySQL database which will allow you to run a query using a regular expression which has built-in support for word boundaries

    Mua-ha-ha-ha-ha-ha!!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    39
    thanks, that works a treat with Pat Phelan's and blindman's method. Thanks to all for contribution.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •