技术问询：寻求匹配邮箱地址中精确姓名的查询方案

Alright, let's break down how to build the SQL query you need to match exact names from your names table to the local portion of email addresses (before the @ symbol), while sticking to your three specific rules.

Core Rules Recap #

First, let's make sure we're aligned on your requirements:

• Match names only in the local part of the email (before @), we'll completely ignore the domain section.
• Names can't be a subset of a longer alphabetic sequence (e.g., "anne" shouldn't match "jeanne" since it's embedded in a longer name with no separating characters).
• If the name is separated by non-alphabetic characters (dots, underscores, numbers, commas, etc.), it doesn't count as a subset (e.g., "anne" should match "joe.anne@domain.com" or "anne_123@domain.com").

The SQL Query #

Here's a query that handles all these conditions, using regular expressions to enforce the exact matching rules:

SELECT n.name, m.mail
FROM names n
JOIN mails m 
  ON SUBSTRING_INDEX(m.mail, '@', 1) 
     REGEXP CONCAT(
       '(^|[^a-zA-Z])', 
       REGEXP_REPLACE(n.name, '([^a-zA-Z])', '\\\\$1'),
       '([^a-zA-Z]|$)'
     );

How It Works #

Let's break down each component to understand why it works:

1. Extract Local Email Part: SUBSTRING_INDEX(m.mail, '@', 1) grabs everything before the @ symbol, ensuring we only check the relevant portion of the email (satisfies your first rule).
2. Regex Pattern for Exact Matching:
   • (^|[^a-zA-Z]): Matches either the start of the local string OR a non-alphabetic character. This ensures the name isn't preceded by a letter, preventing unwanted subset matches like "anne" in "jeanne".
   • REGEXP_REPLACE(n.name, '([^a-zA-Z])', '\\\\$1'): Escapes any special characters in the name (like hyphens or dots) so they're treated literally in the regex, avoiding unexpected matches from regex special characters.
   • ([^a-zA-Z]|$): Matches either a non-alphabetic character OR the end of the local string. This ensures the name isn't followed by a letter, again blocking subset matches.

Testing with Your Sample Data #

Using your provided sample data:

• luis.sepulveda@gmail.com: Matches "luis" (the name is at the start of the local part, followed by a dot—a non-alphabetic separator).
• johnn@mary.com: No matches (the name "mary" is in the domain, which we ignore; the local part "johnn" doesn't match any name in the names table).
• jeanne.darwin@gmail.com: Doesn't match "anne" (since "anne" is embedded in "jeanne" with no non-alphabetic separator between them).
• joe.anne@example.com: Does match "anne" (the name is separated by a dot, so it's not considered a subset of a longer name).

Quick Notes #

• Case Sensitivity: If you need case-insensitive matching (e.g., match "Luis" to "luis.sepulveda@..."), you can adjust the regex or use a case-insensitive collation (like COLLATE utf8_general_ci in MySQL).
• Database Variations: Regex syntax and functions vary slightly between databases (MySQL vs. PostgreSQL vs. SQL Server). If you're using a different DB, you might need to tweak the regex logic a bit (e.g., escaping rules or function names).

内容的提问来源于stack exchange，提问作者Ivan Buttinoni