PostgreSQL: How to Capitalize First Letter of Each Word in Field

Often in PostgreSQL you may want to capitalize the first letter of each word in a field.

You can use the INITCAP() function with the following syntax to do so:

SELECT *,
       INITCAP(team) AS new_team
FROM athletes;

This particular example will capitalize the first letter of each word in the team field of the athletes table and save the results to a new field named new_team.

The following example shows how to use this syntax in practice.

Example: How to Capitalize First Letter of Each Word in PostgreSQL

Suppose that we create the following table named athletes that contains information about various basketball teams:

-- create table named 'athletes'
CREATE TABLE athletes (
  id INTEGER,
  team TEXT
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'dallas mavericks');
INSERT INTO athletes VALUES (0002, 'houston rockets');
INSERT INTO athletes VALUES (0003, 'san antonio spurs');
INSERT INTO athletes VALUES (0004, 'BOSTON celtics');
INSERT INTO athletes VALUES (0005, 'Miami heat');
INSERT INTO athletes VALUES (0006, 'Orlando MAGIC');

-- view all rows in table
SELECT * FROM athletes;

 id |       team        
----+-------------------
  1 | dallas mavericks
  2 | houston rockets
  3 | san antonio spurs
  4 | BOSTON celtics
  5 | Miami heat
  6 | Orlando MAGIC

Suppose that we would like to convert the first letter of each word in the team column to uppercase and the rest of the letters to lowercase.

We can use the INITCAP() function with the following syntax to do so:

SELECT *,
       INITCAP(team) AS new_team
FROM athletes;

 id |       team        |     new_team      
----+-------------------+-------------------
  1 | dallas mavericks  | Dallas Mavericks
  2 | houston rockets   | Houston Rockets
  3 | san antonio spurs | San Antonio Spurs
  4 | BOSTON celtics    | Boston Celtics
  5 | Miami heat        | Miami Heat
  6 | Orlando MAGIC     | Orlando Magic

Notice that the resulting table contains a new column named new_team that capitalizes the first letter of each word in the team column and converts the rest of the letters to lowercase.

You can use similar syntax to capitalize the first letter of each word in a string field in your own table.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Extract Substring Before Specific Character
PostgreSQL: How to Extract Substring Between Two Characters
PostgreSQL: How to Extract Numbers from a String