Messy Customer Names: Cleaning Them With Five String Functions

Three rows, one person: “nguyen an”, “Nguyen An ” and “NGUYEN AN”. Your COUNT DISTINCT is reporting three customers.

The problem

The full_name column is hand-typed across three source systems. The same person shows up three ways, and every count is wrong.

Five functions cover 90% of cases

SELECT
  INITCAP(LOWER(TRIM(full_name))) AS clean_name,
  COUNT(*)
FROM customers
GROUP BY 1;

Common mistakes

1. CONCAT meets NULL

-- MySQL: the whole string becomes NULL if middle_name is NULL
SELECT CONCAT(first, ' ', middle_name, ' ', last) FROM customers;

-- Safe
SELECT CONCAT_WS(' ', first, middle_name, last) FROM customers;

CONCAT_WS skips NULL automatically. In PostgreSQL the || operator also nullifies the whole string — guard it with COALESCE(middle_name,'').

2. Positions start at 1, not 0

SUBSTRING('ABCDEF', 1, 3)   -- 'ABC'
SUBSTRING('ABCDEF', 0, 3)   -- 'AB'  (easy to trip over)

3. Cleaning inside WHERE kills the index

-- Full table scan
WHERE LOWER(TRIM(email)) = 'an@gmail.com'

If you filter this way regularly, clean the data once at load time, or add a normalised column with its own index.

4. Multi-byte characters

In some engines LENGTH returns bytes, not characters. In UTF-8, “Nguyễn” is 6 characters but 8 bytes. Use CHAR_LENGTH when you mean characters.

Splitting a string into columns

-- Extract the email domain
SELECT SPLIT_PART(email, '@', 2) AS domain     -- PostgreSQL
SELECT SUBSTRING_INDEX(email, '@', -1)         -- MySQL
FROM users;

Practice

• LeetCode 1667 — Fix Names in a Table
• LeetCode 1484 — Group Sold Products By The Date
• LeetCode 1965 — Employees With Missing Information
• HackerRank — The Blunder (REPLACE and rounding)
• HackerRank — Name of Employees