GUIDE · DATA

Normalizing text before it goes into a database

By ShengFeng · Published 2026-10-13 · Last updated: 2026-10-16

Most messy data is not wrong, it is merely inconsistent. The same value arrives with a capital letter on one row and not the next, with a stray space, or with an accent typed two ways. Normalizing is the process of deciding one form per field and applying it everywhere. Done deliberately it saves hours of downstream pain; done carelessly it destroys the fields that were never supposed to change.

What normalization means here

In this guide, normalization means imposing a consistent written form on a field: a chosen case, a chosen spacing convention, a chosen date format. It is a data-entry concept, not the Unicode normalization discussed in the Unicode guide, although the two overlap when accented characters are involved. The goal is that two records describing the same thing become byte-identical, so that lookups, joins and duplicate detection all work.

The usual targets

Case rules, and why they are field-specific

There is no universal right answer to “should this be lowercase?”. The answer depends on whether case carries information in that field. Emails and tags do not carry case information, so lowercasing them is safe and useful. Names, identifiers and free-text notes do carry it, so lowercasing them is destructive. Sorting a mixed-case list and looking at the pairs is the fastest way to see which fields have a case problem worth fixing.

Spacing rules

• Always trim. Leading and trailing spaces are never meaningful in a structured field, and they are the most common cause of a lookup that fails to match.
• Collapse internal runs in free-text fields where double spaces are typos, not layout.
• Do not collapse spacing in quoted or formatted text where the layout is part of the content.
• Watch the non-breaking space. It looks like a space and is not one, so trimming at the edges removes it but an internal one survives. That is a common cause of a stubborn non-matching value.

A repeatable sequence

1. Decide the rule per field before touching the data, and write the rule down. A normalization that lives only in someone's head gets undone by the next import.
2. Extract one field per line. Line-based operations need one value per line, so isolate the column first.
3. Run Clean spacing. Trimming removes the padding that would otherwise defeat both duplicate detection and comparison.
4. Apply the case rule. lowercase for emails and tags, uppercase for ISO codes, nothing for names.
5. Run Remove duplicate lines. The true number of distinct values is now visible, and it is usually smaller than expected.
6. Run Sort lines A–Z to make the result easy to eyeball against the original.
7. Compare counts. The line count difference is the number of redundant records found.

Doing this without a script

Normalization is usually described as a job for an import script, and for a large dataset that is right. But a surprising amount of it can be done by hand in a few minutes when the data is small, and the same three steps recur every time.

1. Isolate the column. Copy the field out of the spreadsheet so each value sits on its own line. Line-based operations need one value per line, and a column copied from a spreadsheet arrives that way already.
2. Clean spacing. Trimming removes the padding that would otherwise defeat both comparison and duplicate detection. This is the step people skip most often.
3. Apply one case rule. Lowercase for emails and tags, uppercase for country and postal codes, nothing for names. Do not mix rules within a column.
4. Deduplicate. The number of distinct values is now visible, and it is almost always smaller than the raw row count suggested.
5. Sort and compare. A sorted list can be scanned against the original in seconds, which catches the entry that was normalized into the wrong form.

The whole sequence takes a couple of minutes for a few hundred rows and produces a clean column that can be pasted back into the spreadsheet. For a recurring import the same logic belongs in the script, but the logic is easier to write once you have performed it by hand and seen which steps actually mattered.

Field-level mistakes that come back to bite

• Normalizing a field that is a key. If two tables are joined on a value, normalizing it in one and not the other breaks the join. Apply the rule everywhere the field appears, or nowhere.
• Stripping leading zeros. A postal code, a product code or an account number with a leading zero is not a number, and treating it as one silently changes it. Keep such fields as text.
• Trimming only some rows. A partial cleanup is worse than none, because it makes the remaining inconsistency harder to find rather than more obvious.
• Assuming trimming removes everything invisible. It removes whitespace at the edges, but a zero-width character inside the value survives. If a lookup still fails after trimming, check that first.
• Forgetting the export. A spreadsheet may re-format a value when it saves, undoing the normalization you just applied. Export to plain text before the final check if the format matters.

What must not be normalized

Some fields look like text and behave like an identifier. Changing their case does not tidy them; it invalidates them.

• Passwords and secrets. Case is part of the value. Never run a case conversion over a credential.
• Hashes and digests. A hexadecimal digest may be stored in either case, but it must match whatever the verifier expects.
• Base64 and other encodings. Case is part of the alphabet, and a single changed letter corrupts the payload.
• Coupon, licence and API keys. These are exact-match strings by design.
• Case-sensitive file paths. On some systems two paths differing only in case are two different files.
• Quoted source text. If the text is someone else's words, its exact form is the point.

Write the rules down

The value of normalization comes from repetition, and repetition is exactly what gets lost between one import and the next. Keep the rules in a short document next to the import script: which fields are lowercased, which are trimmed, which are left alone and why. The next person to prepare an import — including your future self — will otherwise make the opposite decision for one field and quietly reintroduce the inconsistency you just spent an afternoon removing.