INITCAP in PostgreSQL: Title-Casing Strings the Right Way

INITCAP is a PostgreSQL function that uppercases the first letter of each word and lowercases the rest. It is handy for normalizing names, cities, and labels into a consistent title-case, but it ships with some non-obvious limits you should know up front.

What INITCAP does

INITCAP takes a string, splits it into words, and for each word raises the first letter to upper case while forcing every other letter to lower case. A word is any run of alphanumeric characters; a word boundary is any non-alphanumeric character (space, period, hyphen, apostrophe, and so on).

SELECT initcap('john DOE');        -- John Doe
SELECT initcap('HELLO world');     -- Hello World
SELECT initcap('order #42 paid');  -- Order #42 Paid

Note that the original interior casing is not preserved. INITCAP always normalizes each word to "first letter up, the rest down", so 'McDONALD' becomes 'Mcdonald'.

Normalizing names and cities

The classic use case is data that arrived from a form or import in arbitrary casing and needs to be displayed cleanly.

-- Tidy up names captured in mixed or all-caps form
SELECT id, initcap(name) AS display_name
FROM users
ORDER BY display_name;

-- Normalize country labels for a report
SELECT initcap(country) AS country, count(*) AS users
FROM users
GROUP BY initcap(country)
ORDER BY users DESC;

You can also write the normalized value back if you want clean data at rest:

-- One-off cleanup of inconsistent name casing
UPDATE users
SET name = initcap(name)
WHERE name <> initcap(name);

Where INITCAP breaks

The big trap is that INITCAP treats every non-alphanumeric character as a word boundary. For real names that is often wrong:

• an apostrophe starts a "new word", so the letter after it gets capitalized;
• hyphens and apostrophes inside a name are all treated as separators;
• interior capitals (as in McDonald) are lost — they get folded down to lower case.

SELECT initcap('o''brien');        -- O'Brien   (B capitalized after the quote)
SELECT initcap('jean-luc PICARD'); -- Jean-Luc Picard
SELECT initcap('mcdonald');        -- Mcdonald  (not McDonald)

Gotcha: INITCAP does not know cultural rules like McDonald, van der Berg, or DeShawn. It always capitalizes after an apostrophe or hyphen, and it drops interior capitals inside a word. For brands and surnames with special spelling, do not rely on INITCAP — store the canonical form separately.

The second subtlety is locale and Unicode. PostgreSQL folds case according to the database locale, and for some pairs (Turkish i/I, for example) the result can differ from what you expect. ASCII names are predictable, but for multilingual data, verify the behavior in your own locale.

If INITCAP only mangles a few rows, do not write its result across the whole table — select the risky rows and fix the rest in place:

-- Apply initcap only where it is safe: skip names with apostrophes or hyphens
UPDATE users
SET name = initcap(name)
WHERE name <> initcap(name)
  AND name !~ '[''-]';

INITCAP in MySQL: emulation

MySQL has no INITCAP. For a single word it is easy to assemble from UPPER, LOWER, and SUBSTRING:

-- MySQL: capitalize only the first letter of a single word
SELECT CONCAT(
  UPPER(SUBSTRING(name, 1, 1)),
  LOWER(SUBSTRING(name, 2))
) AS display_name
FROM users;

For multi-word strings that expression is not enough — you have to process each word. In practice teams solve this with a stored function that walks the string and uppercases the letter after each space, or they push the logic into the application layer. ClickHouse also lacks INITCAP but offers upperUTF8, lowerUTF8, and substring, from which the same emulation is built.

Bottom line: in PostgreSQL INITCAP is great for quick normalization of simple ASCII strings, but do not trust it with surnames that carry apostrophes, hyphens, or interior capitals; in MySQL and ClickHouse you get the same effect by hand with UPPER + LOWER + SUBSTRING.