Postgres String Capitalization

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

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

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

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:

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

t_changed := LOWER(t_original);

Postgres UPPER syntax

t_changed := UPPER(t_original);

Postgres LOWER example

An example of using LOWER in PostgreSQL would look like:

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”.

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_techt_namet_category
0OracleRDB
1PostgreSQLRDB
2MongoDBNoSQL
3PythonLanguage
4PHPLanguage
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:

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_techt_namet_category
0OracleRDB
1PostgreSQLRDB
2MongoNoSQL
3PythonLanguage
4PHPLanguage

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:

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:

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

Which gives us the following fully capitalized values:

t_namet_category
ORACLERDB
POSTGRESQLRDB
MONGONOSQL
PYTHONLANGUAGE
PHPLANGUAGE

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

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

Which yields the following set of records:

t_namet_category
oraclerdb
postgresqlrdb
mongonosql
pythonlanguage
phplanguage

Concatenation, left, right, and len

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

id_techt_namet_category
0OracleRDB
1PostgreSQLRDB
2MongoNoSQL
3PythonLanguage
4PHPLanguage

We next build the following SQL query view:

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_namet_category
OracleRdb
PostgresqlRdb
MongoNosql
PythonLanguage
PhpLanguage

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.

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.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.