i need to search through a database for whole words... this search gives me almost exactly what i need.. SELECT * FROM contacts WHERE last_name LIKE '#search_phrase#' OR last_name LIKE '%[^a-zA-z]#search_phrase#' OR last_name LIKE '#search_phrase#[^a-zA-z]%' OR last_name LIKE '%[^a-zA-z]#search_phrase#[^a-zA-z]%' Code (markup): So as you can see i'm looking for whole words within a field that contains multiple words...... the field could contain the exact search phrase, end with it, start with it or it could be somewhere in the middle.. just by looking at this query i assume there must be a better way.. all i'm really looking for is that the search phrase is surrounded by whitespace or start or end characters any ideas?
For regular expression, instead of 'LIKE', use 'RLIKE' or 'REGEXP'. The regex for 'word boundary' is '\b'. So it should be: SELECT * FROM contacts WHERE last_name RLIKE '%\b#search_phrase#\b%';
thanks for the suggestion but unfortunately it didn't help since i'm using mssql server... which apparently doesn't support regular expressions at all
You can look into full text searching for mysql at http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html.
Ketan9 - the OP has mentioned that he is using MSSQL Server, and so mysql text search documentation may not be of much help to him.