/ ID: 1029 Title: Postgres String Capitalization 1029 Meta Description: This article shows how to best address Postgres String Capitalization. Code samples included. Meta Keywords: Postgres String Capitalization Author: orkb Template: Unstructured Tutorial Categories: PostgreSQL Tags: PostgreSQL, String, Capitalization Status: Published /

Introduction

The purpose here is to learn how best to use the various Postgres string capitalization functions to capitalize characters or change to lowercase. The functions we'll explore here include Lower, Upper, Concat, Left, Right, Len, and Initcap.

Prerequisites

• Beginner-level understanding of writing SQL with PostgreSQL or a similar relational database system such as Oracle, MS SQL Server, or MySQL.
• Knowledge of the use of the most basic SQL (PL/SQL) statements, such as SELECT, UPDATE, INSERT, FROM, etc.
• Understanding of what a String is and what concatenation means. We'll start out with a brief description of what a string is:

What is a Postgres string?

Otherwise known as text or a text data type in Postgres, "string" is named as it is because it is the representation of stringing characters together. In some ways a string is the opposite of a numeric data type. In Postgres, when we add a text column to a table, we can designate the size of that column in terms of bytes. This means, if we know our text column will be for storing phone numbers, we know to define that column's size as 32 bytes will be safe enough size to store all types of phone numbers, including area codes. We always surround strings with either single or double quotes.

Example of string in Postgres

sql t_phone := '512-123-4567';

Now that we have at least a basic understanding of what a string is, let's take a quick tour of what concatenation is and how it works in PostgreSQL.

Concatenation in Postgres

The Postgres CONCAT function combines two or more strings of characters. There are two methods in PostgreSQL to concatenate strings. We'll give a brief overview of both.

Syntax of Postgres Concat

SQL t_str_combined := CONCAT(t_string1, t_string2);

Notice, we fed the CONCAT function two parameters? It can take many more. Taking the syntax above and making the example just a bit more real:

Example of Postgres Concat

SQL t_name_first := "John"; t_name_last := "Doe"; t_name_full := CONCAT(t_name_first, " ", t_name_last);

The results are that t_name_full will come to have a value of "John Doe".

Postgres Concat shorthand

A short hand method of accomplishing the same purpose of string concatenation in PostgreSQL:

SQL t_name_first := "John"; t_name_last := "Doe"; t_name_full := t_name_first || " " || t_name_last);

Now that the prerequisites are out of the way, let's move on to the main course of Postgres functions for string capitalization, otherwise known as manipulating string case or character case.

Postgres LOWER and UPPER

The LOWER function in Postgres returns lowercase characters of any character(s) submitted as a parameter to the function. In other words, if you feed the function something like, "YOUR SQL IS SO RAD", the LOWER() function will return "your sql is so rad".

Postgres LOWER syntax

SQL t_changed := LOWER(t_original);

Postgres UPPER syntax

SQL t_changed := UPPER(t_original);

Postgres LOWER example

An example of using LOWER in PostgreSQL would look like:

SQL t_results := LOWER("YOUR SQL IS SO RAD");

Postgres UPPER example

Similarly, the UPPER function returns an uppercase version of any character(s) fed to the function. So if you give the function a value of "my Tesla rocks", the UPPER() function will return "MY TESLA ROCKS".

SQL t_results := UPPER("my Tesla rocks");

Postgres LOWER in SQL example

Here is an example in SQL of how LOWER works. Let's say we want uniform data returned from Postgres so that we can do various comparisons in our code. In other words, if a user is searching for "python," (and they did not specify "match case") we want search results to return "PYTHON", "Python", and "python", no matter what version the user typed into the search field in our application.

Our test table with already filled data:

| id_tech | t_name | t_category | |---------|------------|------------| | 0 | Oracle | RDB | | 1 | PostgreSQL | RDB | | 2 | MongoDB | NoSQL | | 3 | Python | Language | | 4 | PHP | Language |

SQL SELECT LOWER(t_name) FROM tbl_tech;

That SQL will return the following results:

| t_name | |------------| | oracle | | postgresql | | mongodb | | python | | php |

Postgres UPPER in SQL example

Let's do the same with UPPER:

SQL SELECT UPPER(t_name) FROM tbl_tech;

Returns the following recordset:

| t_name | |------------| | ORACLE | | POSTGRESQL | | MONGODB | | PYTHON | | PHP |

Use UPPER and LOWER in Postgres

Goal: modify column values where we are unsure of what capitalization they may have and we want to combine, say t_name and t_category, to be concatenated into one string (word) and we want consistency in how the data is returned, in terms of case. First, let's look at our full table:

| id_tech | t_name | t_category | |---------|------------|------------| | 0 | Oracle | RDB | | 1 | PostgreSQL | RDB | | 2 | Mongo | NoSQL | | 3 | Python | Language | | 4 | PHP | Language |

Look at the first row of data. There we have "Oracle" and "RDB" in the t_name and t_category columns (fields), respectively. If we query the data like so:

SQL SELECT t_name , t_category FROM tbl_tech;

We get:

| t_name | |------------| | Oracle | | PostgreSQL | | Mongo | | Python | | PHP |

But we want uniformity in the case, so we use UPPER to force the case to upper:

SQL SELECT UPPER(t_name) , UPPER(t_category) FROM tbl_tech;

Which gives us the following fully capitalized values:

| t_name | t_category | |------------|------------| | ORACLE | RDB | | POSTGRESQL | RDB | | MONGO | NOSQL | | PYTHON | LANGUAGE | | PHP | LANGUAGE |

If instead we want our uniformity to be in lower case instead, we change our SQL to use the Lower() function instead of Upper():

SQL SELECT LOWER(t_name) , LOWER(t_category) FROM tbl_tech;

Which yields the following set of records:

| t_name | t_category | |------------|------------| | oracle | rdb | | postgresql | rdb | | mongo | nosql | | python | language | | php | language |

Concatenation, left, right, and len

First, a reminder of the contents of our test dataset called "tbl_tech":

| id_tech | t_name | t_category | |---------|------------|------------| | 0 | Oracle | RDB | | 1 | PostgreSQL | RDB | | 2 | Mongo | NoSQL | | 3 | Python | Language | | 4 | PHP | Language |

We next build the following SQL query view:

SQL SELECT UPPER(LEFT(t_name,1)) || LOWER(RIGHT(t_name,LEN(t_name)-1)) || ' - ' || UPPER(LEFT(t_category,1)) || LOWER(RIGHT(t_category,LEN(t_category)-1)) FROM tbl_tech;

Returns this data:

| t_name | t_category | |------------|------------| | Oracle | Rdb | | Postgresql | Rdb | | Mongo | Nosql | | Python | Language | | Php | Language |

Postgres Initcap

We can simplify even more by using INITCAP; another Postgres string function. Why didn't we use this before? Because the above examples help us understand how to use UPPER and LOWER, which will at times be useful in ways that do not require use of the Initcap() function.

SQL SELECT INITCAP(t_name) || ' - ' || INITCAP(t_category) FROM tbl_tech;

Conclusion

We learned in this tutorial how to use various Postgres string capitalization functions to capitalize characters or change them to lower case. The functions we'll explore here include Lower, Upper, Concat, Left, Right, Len, and Initcap. Code (SQL) samples are included throughout to demonstrate various ways these functions can be used to capitalize strings of characters or single characters within our queries in PostgreSQL.