How to exclude field names from MySQL search and match only actual data values

Other Questions MySQL
1

I’m working with a database where customer info is stored in a single column using this format: name:Sarah|lastname:Smith|phone:987654321|email:[email protected] and so on.

I want to build a live search that shows results as users type, but I only want to match the actual data (like “Sarah”) and not the field labels (like “name”).

The issue is when someone types “name” they get matches because of the field labels, not because a customer is actually named “name”. This makes the search results confusing.

I’ve thought of two approaches but both have problems:

  1. Adding a colon before the search term like “:sarah” to avoid matching labels. But this breaks when searching for hyphenated names like “marie-claire” because it won’t find the “claire” part.

  2. Making a separate search column with just the values like Sarah|Smith|987654321|[email protected]. The problem is keeping it updated when customer data changes since fields might be missing or in different orders.

Is there a better way to search the existing column format while ignoring the field names?

2

Regex can handle this without changing your data structure. Try (?<=:)[^|]*search_term[^|]* - it’ll only match text after a colon and before the next pipe. So searching “Sarah” finds :Sarah but skips “name:” completely. I’ve done this on a similar project and it worked great for live search. If you’re on MyISAM or newer InnoDB, throw a fulltext index on that column for better performance. The regex also fixes your hyphenated names since it matches anywhere in the value field. Just heads up - regex queries get slow with big datasets, so you might want to limit results or filter first.