0

I'm working with a MySQL database that hold metaphone keys of a string (peoples names) in a VARCHAR field but I'm having a problem when it comes to pulling out individuals with multiple names.

As an example, take the metaphone key for "Mary Ann", which is stored in the database (space delimited) as "MR AN". If I wanted to pull out all "Mary's" (MR), I could do :-

WHERE firstname = 'MR';

But this wouldn't pull out "Mary Ann" (MR AN) as well which I want to do. I could try :-

WHERE firstname LIKE '%MR%';

But this has the danger of possibly pulling out other metaphone keys with "MR" in them that aren't Marys. It's a similar issue with short names like "Lee" where the metaphone key is just "L". Doing a "LIKE '%L%'" could potentially return millions of other names ...

So my question is what is the best approach to this? How can I pull out individual metaphone keys that are in one field, space delimited? Do I need some sort of regular epression within a select statement for this?

Gary
  • 107
  • 10
  • I'll try answering my own question ... something like this? WHERE firstname REGEXP '[[:<:]]L[[:>:]]'; – Gary Jun 01 '19 at 21:57
  • If you wanted MR why would you search for '%MR'? You would search for 'MR %', right? Similarly, LIKE 'L %' would only return L metaphones. – Strawberry Jun 02 '19 at 05:47
  • Give about 3 examples of something that you want to match, and about that many that should not. (I'm thinking that "metaphone" is irrelevent to the REGEXP question. – Rick James Jun 03 '19 at 04:32

0 Answers0