1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    London, UK
    Rep Power

    Regular Expression for Whole Words

    I am try to build a query which only matches whole words and so far I've got this.

    SELECT *
    FROM tblSearchWords
    WHERE CorrectSpelling LIKE '%[^a-zA-Z0-9]blah[^a-zA-Z0-9]%'
    This will return rows which contain the string 'blah' without any numeric or alphanumeric characters beside it. However it doesn't return the rows where 'blah' is either at the start or end of the string as it expects any character except a-zA-Z0-0.

    Is there any way to accept string when there is nothing on either side as well?

  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2002
    Stevens Point, Wisconsin
    Rep Power
    It really doesn't look as though only LIKE would support your situation. I don't see a way to optionally check for a pattern match - especially if it's at the beginning of a string. Regex would be the way to go.

    One solution, combining LIKE with other methods, would be to just get the charindex of 'blah'. If it's greater than 0, check if the character before it is LIKE [^a-zA-Z0-9]

    and if the position of 'blah' is less than (the len of the column - len of 'blah'), then check the character right after it it is LIKE [^a-zA-Z0-9]

IMN logo majestic logo threadwatch logo seochat tools logo