Introduction to PostgreSQL INITCAP() function

TheINITCAP() function converts the first letter of each word in a string to uppercase and the rest to lowercase. In other words, theINITCAP() function converts a string to a proper case.

Technically, words are defined as sequences of alphanumeric characters separated by non-alphanumeric characters.

Here’s the basic syntax of theINITCAP() function:

INITCAP(text)

In this syntax, thetext is a string you want to convert to a proper case. Its type can beCHAR,VARCHAR, orTEXT.

TheINITCAP() returns the text in a proper case. It returnsNULL if thetext isNULL.

To convert a string to lowercase, you use theLOWER() function. To convert a string to uppercase, you use theUPPER() function.

PostgreSQL INITCAP() function examples

Let’s explore some examples of using theINITCAP() function.

1) Basic PostgreSQL INITCAP() example

The following example uses the INITCAP() to convert the string ‘hello john’ to the proper case:

SELECT INITCAP('hello john');

Output:

initcap------------ Hello John(1 row)

2) Using PostgreSQL INITCAP() with table data example

In practice, you often use theINITCAP() function to format blog titles, author names, and so on. For example:

First,create a new table calledblog_posts and insert some rows into it:

CREATE TABLE blog_posts(    id SERIAL PRIMARY KEY,    title VARCHAR(255)NOT NULL);INSERT INTO blog_posts(title)VALUES    ('getting started with postgresql'),    ('advanced postgresql queries'),    ('postgresql performance optimization'),    ('postgresql data modeling techniques'),    ('using postgresql in web development')RETURNING *;

Output:

id |                title----+-------------------------------------  1 | getting started with postgresql  2 | advanced postgresql queries  3 | postgresql performance optimization  4 | postgresql data modeling techniques  5 | using postgresql in web development(5 rows)

Second, use theINITCAP() function toupdate the titles in theblog_posts table to the proper case:

UPDATE blog_postsSET title = INITCAP(title)RETURNING *;

Output:

id |                title----+-------------------------------------  1 | Getting Started With Postgresql  2 | Advanced Postgresql Queries  3 | Postgresql Performance Optimization  4 | Postgresql Data Modeling Techniques  5 | Using Postgresql In Web Development(5 rows)UPDATE 5

Summary

• Use the PostgreSQLINITCAP() function to convert a text to the proper case.

Last updated onJanuary 29, 2024

On this page

• Introduction to PostgreSQL INITCAP() function
• PostgreSQL INITCAP() function examples
• Summary