Skip to content
Koltrix
Start free trialOpen app
Email API

Normalizing email addresses in your user table: case, dots and Unicode

Duplicate accounts, failed logins and lost resets often trace back to how an address was stored. A safe way to normalize, compare and keep the original.

Koltrix Team6 min read
White spiral notebook on a wooden table
Photo by Kelly Sikkema on Unsplash
On this page(11 sections)
  1. The rule to start from
  2. What the standard says, and what really happens
  3. A conservative normalization function
  4. What not to normalize
  5. The Gmail question
  6. Storing it
  7. Using it everywhere
  8. Sending to the stored address
  9. Migrating existing data
  10. Tests worth writing
  11. Key takeaways

A customer signs up as [email protected]. A week later they try to log in with [email protected] and your app says no account exists. Another customer asks for a password reset, you send it to the address stored in the database, and it never arrives because of a stray space at the end. A third person ends up with two accounts, one for each way they typed their address.

These bugs share a cause: the app treated an email address as a plain string, and never decided what makes two addresses "the same". This guide gives a safe policy for storing, comparing and matching addresses, with a small amount of SQL, and explains where it is tempting to be too clever.

The rule to start from

Keep two values for every address:

  • The address as the person gave it, trimmed of whitespace. Use it for display and, in most cases, for sending.
  • A normalized form, derived by a fixed, conservative function. Use it only for lookups and uniqueness.

Never overwrite the first with the second. If you only store a normalized version, you lose the spelling the customer chose, and if your normalization later turns out to be wrong, you cannot recover.

What the standard says, and what really happens

An email address has two parts: a local part before the @ and a domain after it.

  • The domain is case-insensitive. Example.COM and example.com are the same place. Lowercasing the domain is always safe.
  • The local part is, in theory, case-sensitive. The standard allows a receiving system to treat Maya and maya as different mailboxes. In practice, nearly every provider treats them as the same, and almost no one runs a system where they differ.
  • Most providers ignore case in the local part, but the rules vary elsewhere. That is where overly clever normalization causes harm.

So there is one universally safe step, and several that are only safe with knowledge of the specific provider.

A conservative normalization function

Apply these steps in order, and stop there:

  1. Trim whitespace at both ends. Remove non-breaking spaces and zero-width characters that arrive from copy and paste.
  2. Reject control characters, line breaks and multiple @ signs (outside of quoted local parts, which you can refuse for ordinary signup). Line breaks in an address are a classic way to inject extra headers into outgoing mail.
  3. Split at the last @ into local part and domain.
  4. Lowercase the domain.
  5. Convert an international domain name to its ASCII form (the xn-- punycode version), so that the same domain written two ways compares as equal. Use a library for this. Unicode in email addresses explains the background.
  6. Normalize Unicode in the local part to a single form (NFC), so an accented letter written as one character or as a letter plus a combining mark does not create two entries.
  7. Lowercase the local part, as a deliberate decision, because in practice a customer who typed capitals did not mean a different mailbox.

That is the whole function. Notice what is not on the list.

What not to normalize

Dots in the local part. At Gmail's own domain, [email protected] and [email protected] reach the same mailbox. At nearly every other provider, they are different people. If you remove dots for every domain, you will merge two real customers into one account, which is a privacy and security failure. Even for Gmail, think twice, as the next section explains.

The part after a plus sign. [email protected] delivers to [email protected] at providers that support tags, and is a different mailbox at those that do not. Removing tags for every domain breaks people who use them on purpose. See plus addressing and aliases for how customers use them.

Look-alike characters. Replacing letters that look similar, such as a Cyrillic "а" for a Latin "a", is not normalization. It is a security decision. Reject or flag addresses that mix scripts in suspicious ways, and do not silently rewrite them.

Domain aliases. gmail.com and googlemail.com deliver to the same mailbox. Treating them as equal is possible, but it is a policy for a specific provider, not part of general normalization.

The Gmail question

Many apps want to stop people from creating many free accounts by using [email protected], [email protected], or dots. If that is a real problem for you, handle it as an abuse policy, separate from normalization:

  • Keep the normalized key conservative for identity and login.
  • Compute a second, aggressive "abuse key" for a short list of providers whose rules you know, and use it only to flag or limit signups, not to merge accounts.
  • Expect false positives. Two colleagues at a company that uses plus addressing are not duplicates.
  • Be open with customers when you apply it, and offer a way to appeal.

Mixing this up with the identity key is how an honest customer gets "an account with this email already exists" for an address they have never used.

Storing it

A simple pattern is two columns and a unique index on the normalized one:

CREATE TABLE users (
  id            uuid PRIMARY KEY,
  email         text NOT NULL,          -- as entered, trimmed
  email_key     text NOT NULL,          -- normalized, for lookups
  created_at    timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX users_email_key_idx ON users (email_key);

Compute email_key in application code with one shared function, so that signup, login, password reset, invitations and imports all use exactly the same rules. Do not rely on lower(email) in some queries and your function in others. If the function ever changes, you will need a migration to recompute the keys, so keep it simple and versioned.

If your database offers a case-insensitive text type, it can handle the case part for you, but it will not handle domain conversion or Unicode normalization, so a shared function in code is still needed.

Using it everywhere

The same function should be applied at every entry point:

  • Signup and login. Normalize the input, then look up by email_key.
  • Password reset and magic links. Look up by key, then send to the stored email, the address as the person gave it. Password reset email security and magic links vs email OTP cover the other risks in these flows, including not revealing whether an account exists.
  • Invitations. Match an invited address to an existing account by key.
  • Imports and integrations. Normalize on the way in, and report rows that collide.
  • Suppression lists. Store and check suppression entries by normalized key too, or a bounced address will slip through with different capitalization. See suppression list policy.
  • Webhooks and events. When you log which address bounced or complained, match it to your records with the same key.

Sending to the stored address

Normalization is for matching, not necessarily for delivery. Send to the address as entered after trimming, because that is the one the customer saw and typed. Lowercasing the local part for delivery is usually harmless, but there is no benefit to doing it, and for the rare mailbox where it matters, the original is correct.

One exception: for domains with non-ASCII characters, send to the ASCII form unless the receiving side supports internationalized addresses.

Migrating existing data

If your table already holds a mixture of cases and stray spaces:

  1. Add the email_key column without the unique index.
  2. Backfill it with your function.
  3. Find collisions: SELECT email_key, count(*) FROM users GROUP BY 1 HAVING count(*) > 1.
  4. Resolve each pair by hand or by a clear rule, such as keeping the older account and merging the data. Do this carefully: merging accounts is a decision about people, and you should not do it silently.
  5. Add the unique index, then switch lookups to the key.
  6. Update every code path that touches addresses, and add tests.

Tests worth writing

A short table of cases catches nearly everything:

Input Key
[email protected] [email protected]
[email protected] [email protected]
[email protected] [email protected]
[email protected] [email protected] (dots kept)
[email protected] [email protected] (tag kept)
maya@exämple.com [email protected]
[email protected]\r\nBcc: [email protected] rejected
maya@@example.com rejected

Add a test for every bug you meet in production, and run the same set against the function used by your suppression and invitation code.

Key takeaways

  • Store the address as entered and a separate normalized key, and use the key only for lookups and uniqueness.
  • Normalize conservatively: trim, reject control characters, lowercase the domain, convert international domains to ASCII, normalize Unicode and lowercase the local part.
  • Do not remove dots or plus tags for every domain. Treat anti-abuse matching as a separate, limited policy.
  • Use one shared function at signup, login, reset, invitations, imports and suppression, and send to the stored address.
  • Migrate with a backfill, check for collisions and resolve them by hand before adding the unique index.

Start with Koltrix

Your domain, one inbox, and an API that sends.

A team inbox where AI sorts and drafts (nothing is sent without your click), plus the transactional API and SMTP relay your product sends with. 7 days free, no card.

Start your 7-day free trialOpen KoltrixRead the docs
SharePost on XLinkedIn

Keep reading

More in Email API →