Inconsistent capitalization
The same value appears in capitals, in lowercase, and in mixed case, and software treats each as different.
What it looks like
Before
| name | city | |
|---|---|---|
| ANA RUIZ | richmond | Ana.Ruiz@Example.com |
| ben mcallister | NEW YORK | ben@example.com |
| Dee van der Berg | Portland | DEE@EXAMPLE.COM |
After
| name | city | |
|---|---|---|
| Ana Ruiz | Richmond | ana.ruiz@example.com |
| needs a decision | New York | ben@example.com |
| Dee van der Berg | Portland | dee@example.com |
How it happens
- Some people type with caps lock on, some never use the shift key, and nothing at entry enforces a style.
- An older system stored everything in capitals, and its rows were merged with newer ones.
- A blanket capitalization rule was applied earlier and damaged names that do not follow it.
What it breaks
- Exact matching treats differently cased values as different, so groups split and lookups fail.
- Duplicate checks miss records that differ only by case.
- Letters and labels printed from the data look careless, or address people by a name in capitals.
How to spot it
- Count distinct values, then count again after lowercasing. The difference is the size of the problem.
- Look for values that are entirely uppercase or entirely lowercase in a column that is mostly mixed case.
- Check fields used for matching, such as email and codes, before fields used for display.
How to fix it
- Decide the rule per column. Codes and email addresses usually take one case throughout; names and places take the case their owners use.
- For matching, compare on a lowercased copy and leave the displayed value alone.
- Apply capitalization rules to names with care. Particles, prefixes, and abbreviations break any simple rule, and the exceptions need a person or a trusted source.
- Name variantsThe same person or organization appears under several spellings, orders, and abbreviations.
- Email typos and letter caseEmail addresses differ by capitalization, or contain small typing errors that make them undeliverable.
- Inconsistent category labelsA column meant to hold a few fixed values holds many spellings of them.
TermsNormalization, Standardization, String, Canonical value