Retrieving words with 3 or less characters in MySQL

Question

I have a table with a field that contains a bunch of neighborhood names. Some of these neighborhoods have names with 2 or more words. How can I get a list of words that are 3 or less characters and occur in the middle of name with 3 or more words?

For example:

Lake = Do nothing, only 1 word
Golden Lake = Do nothing, only 2 words
Lakes of Gold = Extract "of"

In essence I want to make a list of 'garbage' words to remove when I build metaphone sentences.

Answer 1

SELECT  'Lake of gold' RLIKE '[[:<:]].+[[:>:]].+[[:<:]].{1,3}[[:>:]].+[[:<:]].+[[:>:]]'

Unfortunately, MySQL can only match the regexps, not extract the patterns. You will have to do the filtering in MySQL and extraction on the script side.

Answer 2

SELECT * FROM mytable WHERE mycolumn REGEXP "[[:alnum:]]+[[:space:]]+[[:alnum:]]{1,3}[[:space:]]+[[:alnum:]]+";

will find all entries that contain at least one word of up to 3 characters in between two other words.

You can't extract the words in MySQL directly, but this will filter the relevant rows. You have to do the extraction in a separate step.