In PostgreSQL, we can use the initcap() function to format a string of text to use initial capitals. That is, it converts the first letter of each word to upper case and the rest to lower case.

Syntax

The syntax goes like this:

initcap ( text )

Where text is the text to be converted to initial caps.

Example

SELECT initcap('Bay of pigs');

Result:

Bay Of Pigs

Here’s an example that starts with all uppercase text:

SELECT initcap('PRACHUAP KHIRI KHAN');

Result:

Prachuap Khiri Khan

Words are sequences of alphanumeric characters separated by non-alphanumeric characters:

SELECT 
    initcap('shrink-wrap') AS "1",
    initcap('shrink.wrap') AS "2",
    initcap('shrink,wrap') AS "3";

Result:

+-------------+-------------+-------------+
|      1      |      2      |      3      |
+-------------+-------------+-------------+
| Shrink-Wrap | Shrink.Wrap | Shrink,Wrap |
+-------------+-------------+-------------+

Numbers are alphanumeric, so capitalisation of subsequent words will depend on whether or not there’s another character that’s non-alphanumeric:

SELECT 
    initcap('shrink1wrap') AS "1",
    initcap('shrink1 wrap') AS "2";

Result:

+-------------+--------------+
|      1      |      2       |
+-------------+--------------+
| Shrink1wrap | Shrink1 Wrap |
+-------------+--------------+

Database Example

Here’s an example of converting the results of a database query to initial caps:

SELECT 
    title,
    initcap(title)
FROM film
LIMIT 10;

Result:

+------------------+------------------+
|      title       |     initcap      |
+------------------+------------------+
| ACADEMY DINOSAUR | Academy Dinosaur |
| ACE GOLDFINGER   | Ace Goldfinger   |
| ADAPTATION HOLES | Adaptation Holes |
| AFFAIR PREJUDICE | Affair Prejudice |
| AFRICAN EGG      | African Egg      |
| AGENT TRUMAN     | Agent Truman     |
| AIRPLANE SIERRA  | Airplane Sierra  |
| AIRPORT POLLOCK  | Airport Pollock  |
| ALABAMA DEVIL    | Alabama Devil    |
| ALADDIN CALENDAR | Aladdin Calendar |
+------------------+------------------+

Here I used the pagila sample database. This database stores film names in uppercase characters in the title column. In this case I used the initcap() function to convert the title column to use initial capitals.