Sql search engine - ignoring punctuation

I’m putting together a simple search engine and almost have it working the way I want it to. My only problem is that if a word is in contact with punctuation (such as “test.” or “test,”) it will not appear in my search results. What is the best way to handle this problem? I don’t want to enable it to find sub-words (ie “run” in “running”). I just need to ignore all punctuation when doing comparisons in the sql. I know that mysql has regexp support but i’m not sure if its possible to integrate it with what I already have or if I need to rebuild my sql from scratch using regexp instead of LIKE.

This is what I have right now:


OR dept LIKE "% ' . $word . ' %" OR dept LIKE "% ' . $word . '" OR dept LIKE "' . $word . ' %" OR dept LIKE "' . $word . '"'
. ' OR organization LIKE "% ' . $word . ' %" OR organization LIKE "% ' . $word . '" OR organization LIKE "' . $word . ' %" OR organization LIKE "' . $word . '"'
. ' OR address LIKE "% ' . $word . ' %" OR address LIKE "% ' . $word . '" OR address LIKE "' . $word . ' %" OR address LIKE "' . $word . '"'

consider using “whole word” mysql regular expression instead of LIKE

OR dept REGEXP '[[:<:]]$word[[:>:]]'
OR organization REGEXP '[[:<:]]$word[[:>:]]'
OR address REGEXP '[[:<:]]$word[[:>:]]'

I tried the word boundry regexp earlier and was having some trouble with it. When using this:

OR dept REGEXP "[[:<:]]ca[[:>:]]" OR organization REGEXP "[[:<:]]ca[[:>:]]" OR address REGEXP "[[:<:]]ca[[:>:]]"

It appears to return subwords. There are almost twice as many results when I use this. Looking through some of the results of the above example, several do not contain the full word “ca” but do contain the order of letters “ca” in words such as canada.