I have a form that collects first_name and last_name from users. I need to search an Airtable database of event attendees using the airtable.js library. The problem is that many attendees have hyphenated surnames like smith-johnson in the database, but users often only enter the first part like smith in the form.
When someone enters just the first part of a hyphenated surname, Airtable can’t find a match. I tried using REGEX with filterByFormula to match the partial surname, but it’s not working:
`(AND({first_name} = "${first_name}",SEARCH(REGEX_EXTRACT("${last_name}",'[^-]*'),{last_name})>0))`
This approach isn’t giving me results. How can I modify the query to successfully match partial hyphenated surnames? Any suggestions would be helpful.
Your regex is way too complicated for this. You don’t need REGEX_EXTRACT - you’re just checking if the partial name exists in the full hyphenated name. Use SEARCH instead:
filterByFormula: `AND({first_name} = "${first_name}", SEARCH("${last_name}", {last_name}) > 0)`
This finds records where the entered last name appears anywhere in the database field. I’ve used this exact pattern for partial matching and it works great. SEARCH returns the position if it finds the substring, or an error if not - so > 0 confirms there’s a match. Just make sure your last_name variable is properly escaped and doesn’t have special characters that’ll break the formula.
Honestly, just use STARTS_WITH - it’s way simpler than regex. filterByFormula: AND({first_name} = "${first_name}", STARTS_WITH({last_name}, "${last_name}")) does exactly what you need. Works perfectly for hyphenated names since it matches from the beginning.
Had this exact problem last year building a check-in system. SEARCH works but you’ll get false positives - like searching “son” and matching “johnson-peterson”. Here’s what fixed it for me using FIND:
filterByFormula: `AND({first_name} = "${first_name}", OR(FIND("${last_name}-", {last_name}) = 1, {last_name} = "${last_name}"))`
This checks if the partial name starts the field and has a hyphen after it, OR matches exactly for regular names. The = 1 makes sure it only matches at the beginning. I’ve used this for months - handles both hyphenated and regular surnames perfectly.
Both solutions work but miss edge cases. I hit this building an employee directory where people had double hyphens or weird spacing around hyphens.
What saved me was normalizing the search term first, then using a more flexible pattern:
const normalizedLastName = last_name.toLowerCase().trim();
filterByFormula: `AND({first_name} = "${first_name}", OR(LOWER(LEFT({last_name}, ${normalizedLastName.length})) = "${normalizedLastName}", FIND("${normalizedLastName}", LOWER({last_name})) = 1))`
This handles case sensitivity and checks if your partial name matches the start of the database field. LEFT grabs exactly the number of characters you’re searching for.
I also added a fallback that strips special characters if the main search fails:
const cleanName = last_name.replace(/[^a-zA-Z]/g, '').toLowerCase();
Then search against a cleaned version of the database field. This caught names with weird spacing or punctuation that users typed differently than what was stored.