mysqlregexmetaphone

Retrieving words with 3 or less characters in MySQL

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.

Solution 
  • 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.