Postgres String Capitalization
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.
- 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
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
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
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:
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
Postgres UPPER syntax
Postgres LOWER example
An example of using LOWER in PostgreSQL would look like:
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”.
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:
That SQL will return the following results:
Postgres UPPER in SQL example
Let’s do the same with UPPER:
Returns the following recordset:
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:
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:
But we want uniformity in the case, so we use UPPER to force the case to upper:
Which gives us the following fully capitalized values:
If instead we want our uniformity to be in lower case instead, we change our SQL to use the Lower() function instead of Upper():
Which yields the following set of records:
Concatenation, left, right, and len
First, a reminder of the contents of our test dataset called “tbl_tech”:
We next build the following SQL query view:
|| ' - '
Returns this data:
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.
|| ' - '
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started