PostgreSQL INITCAP Function

Summary: in this tutorial, you’ll learn how to use the PostgreSQL INITCAP function to convert a string to initial caps.

Introduction to the PostgreSQL INITCAP function #

The INITCAP function allows you to capitalize the first letter of each word in a string and convert other letters to lowercase. In other words, the INITCAP function converts a string to initial caps.

The INITCAP function defines a word as a series of alphanumeric characters separated by non-alphanumeric characters.

Here’s the syntax of the INITCAP function:

INITCAP( string )Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The INITCAP function takes one parameter:

• string is the text input in which you want to convert to init caps.

The INITCAP function returns a new string with each word’s first letter converted to uppercase and all other letters converted to lowercase.

The INITCAP function returns NULL if the input string is NULL.

The INITCAP function is handy for formatting strings such as names and places.

Basic PostgreSQL INITCAP function #

The following query uses the INITCAP function to convert the first letter of each word in the string "pg tutorial" to uppercase and the rest to lowercase:

SELECT
  INITCAP('pg tutorial') result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

   result
-------------
 Pg TutorialCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Using the INITCAP function with table data #

First, create a new table called product_groups to store product groups:

CREATE TABLE product_groups (
  product_group_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_group_name VARCHAR
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Second, insert some rows into the product_groups table:

INSERT INTO
  product_groups (product_group_name)
VALUES
  ('smart watches'),
  ('wireless audio'),
  ('mobile phones')
RETURNING
  product_group_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 product_group_name
--------------------
 smart watches
 wireless audio
 mobile phonesCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, use the INITCAP function to format the product group names:

SELECT
  INITCAP(product_group_name)
FROM
  product_groups
ORDER BY
  product_group_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

    initcap
----------------
 Mobile Phones
 Smart Watches
 Wireless AudioCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Finally, update the product group names using the INITCAP function in an UPDATE statement:

UPDATE product_groups
SET
  product_group_name = INITCAP(product_group_name)
RETURNING
  product_group_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 product_group_name
--------------------
 Smart Watches
 Wireless Audio
 Mobile PhonesCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• Use the PostgreSQL INITCAP function to convert a string to initial caps.