i made a simple search engine for a company intranet site that searches different areas of the intranet...
a user might search for employees with the query "tom" and hopefully "tom tomerson" would show up.
the problem is "notTOM" is showing up too... if we are talking about just first names, the query is something like
SELECT blah FROM blah WHERE first_name LIKE "%tom%"
i would like it to return people with just the full word tom.... i could do this:
SELECT blah FROM blah WHERE first_name LIKE "tom" OR first_name LIKE "% tom" OR first_name LIKE "tom %" OR first_name LIKE "% tom %"
but that wouldn't include "tom-o" or something similary stupid. what i need is to give a set of seperators like [ .,;'"?!$&/:] like a regular expression. is that syntax available with SQL and more specifically mySQL?
i would like to do something like
SELECT blah FROM blah WHERE first_name LIKE "%[ .,;'"?!$&/:\n\t]tom[ .,;'"?!$&/:\n\t]%"
hopefully it would work where "tom" would match "i like tom." but tom wouldn't match "tommorow is 2 hours from now"
i could code this up to generate a query with 10000 LIKE statements exhausting the list, but there has to be a better way.
thanks for reading.
mike
a user might search for employees with the query "tom" and hopefully "tom tomerson" would show up.
the problem is "notTOM" is showing up too... if we are talking about just first names, the query is something like
SELECT blah FROM blah WHERE first_name LIKE "%tom%"
i would like it to return people with just the full word tom.... i could do this:
SELECT blah FROM blah WHERE first_name LIKE "tom" OR first_name LIKE "% tom" OR first_name LIKE "tom %" OR first_name LIKE "% tom %"
but that wouldn't include "tom-o" or something similary stupid. what i need is to give a set of seperators like [ .,;'"?!$&/:] like a regular expression. is that syntax available with SQL and more specifically mySQL?
i would like to do something like
SELECT blah FROM blah WHERE first_name LIKE "%[ .,;'"?!$&/:\n\t]tom[ .,;'"?!$&/:\n\t]%"
hopefully it would work where "tom" would match "i like tom." but tom wouldn't match "tommorow is 2 hours from now"
i could code this up to generate a query with 10000 LIKE statements exhausting the list, but there has to be a better way.
thanks for reading.
mike