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.
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:
1 | 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:
1 2 3 4 5 6 7 8 9 | 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.
1 2 3 4 5 6 7 8 9 | 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.
1 | 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:
1 2 3 4 5 6 7 | 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:
1 | 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:
1 2 3 4 5 6 7 8 | 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.