PostgreSQL Tutorial: Letter Case Functions

Summary: In this tutorial we will show you how to use the LOWER, UPPER and INITCAP functions to convert a string expression, values in a column, etc., to lowercase, uppercase, and proper case.

Table of Contents

PostgreSQL LOWER function

To convert a string, an expression, or values in a column to lower case, you use the LOWER case function. The following illustrates the syntax of the LOWER function:

LOWER(string_expression)

The LOWER function accepts an argument that is a string e.g., char, varchar, or text and converts it to lower case format. If the argument is string-convertible, you use the CAST function to explicitly convert it to a string.

The following statement uses LOWER function and CONCAT_WS function to get the full names of customers:

SELECT
	concat_ws (
		', ',
		LOWER (last_name),
		LOWER (first_name)
	) as name
FROM
	customer
ORDER BY last_name;

The following statement uses the CONCAT_WS function and LOWER function to get the title, description, and year of films in the film table. Because release_year is a number, therefore we have to use type cast to convert it to a string.

Note that this example is just for demonstration only. You can remove the LOWER function that applies to the release_year column because it is not necessary.

SELECT
	CONCAT_WS (
		' - ',
		title,
		LOWER (description),
		LOWER (CAST(release_year AS TEXT))
	) AS film_info
FROM
	film;

PostgreSQL UPPER function

To convert a string into upper case, you use PostgreSQL UPPER function. The following illustrates the syntax of the UPPER function.

UPPER(string_expression)

Like the LOWER function, the UPPER function accepts a string expression or string-convertible expression and convert it to a upper case format. In case the argument is not a string, you must use the CAST function to explicitly convert it.

The following statement uses the CONCAT function and UPPER function to return the full name of staff in the upper case:

SELECT
	CONCAT (
		UPPER (first_name),
		UPPER (last_name)
	) as full_name
FROM
	staff;

PostgreSQL INITCAP function

The INITCAP function converts a string expression into proper case or title case, which the first letter of each word is in uppercase and the remaining characters in lowercase.

The following illustrates the syntax of the INITCAP function:

INITCAP(string_expression)

We often use INITCAP function to format blog title, author name, etc. For example, the following statement formats the names of customers in proper case:

SELECT
	INITCAP(
		CONCAT (first_name, ' ', last_name)
	) AS full_name
FROM
	customer
ORDER BY
	first_name;

In this tutorial, we have introduced you to three helpful string functions including LOWER, UPPER, and INITCAP to convert string cases.

See more

PostgreSQL Tutorial: String Functions

PostgreSQL Documentation: String Functions and Operators