Postgres String Case Lower and Upper in SQL

Introduction

In this article, we will talk about the Postgres string case LOWER and UPPER in SQL to change characters from three perspectives, including:

  • What? What do the LOWER and UPPER string functions do?
  • How? How do we use these functions in our SQL scripts?
  • Why? In what circumstances do we need these functions? Real world use cases included.

Prerequisites

  • High level understanding of how to write SQL with PostgreSQL (or Oracle, MS SQL Server, MySQL, etc.), using the free PG Admin tool or some other relational db administration tool, or even by using a coding (compiled or not, object-oriented or not) language (like PHP, Python, C#, Java, VB.Net, ASP.Net, Javascript, Ruby, Node.js, etc.) that allows a connection to your db as well as a method for sending it PL/SQL statements, to query or make changes to your data.
  • Knowledge of the use of the most basic SQL (PL/SQL) statements, including SELECT and FROM.
  • Understanding of what a String is and what concatenation means.

What are the LOWER and UPPER functions?

The PostgreSQL LOWER function returns lower case characters of any string fed to the function. In other words, if you feed the function something like, “I LOVE SQL,” the LOWER() function will return “i love sql.” Likewise, the UPPER function returns upper case characters of any string fed to the function. So if you seed the function with something like, “I LOVE PYTHON,” the UPPER() function will return “I LOVE PYTHON.”

How do we use these functions?

Syntax

-- LOWER
t_newString := LOWER(t_oldString);

-- UPPER
t_newString := UPPER(t_oldString);

Let’s look at an example in SQL of how LOWER works. In this case, we want uniform results returned from our database so that we can do various kinds of comparisons in our code. In other words, if a user is searching for “oracle,” (and they did not specify “match case”) we want search results to return both “Oracle” and “oracle”, whatever case the user typed into the search box.

Our test table with already filled data:

id_techt_namet_category
0OracleRDB
1MongoNoSQL
2MySQLRDB
3PostgreSQLRDB
4PythonLanguage
5PHPLanguage
SELECT LOWER(t_name) FROM technologies

The above query will return the following results:

t_name
oracle
mongo
mysql
postgresql
python
php

Let’s do the same for the UPPER function:

SELECT UPPER(t_name) FROM technologies

Returns the following recordset:

t_name
ORACLE
MONGO
MYSQL
POSTGRESQL
PYTHON
PHP

Why use the LOWER and UPPER functions?

Combine UPPER and LOWER in a Real-world use case

One use case would be if we want to modify a string or strings where we are unsure of what capitalization they may have and we want to combine, say technology name and technology category, to be one string (word) and we want complete consistency in how we receive this data. First, let’s look at our full table of data:

id_techt_namet_category
0OracleRDB
1MongoNoSQL
2MySQLRDB
3PostgreSQLRDB
4PythonLanguage
5PHPLanguage

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 technologies

We get:

t_name
Oracle
Mongo
MySQL
PostgreSQL
Python
PHP

But we want uniformity in the capitalization (case), so we let’s use our trusty UPPER() function to force uppercase:

SELECT
  UPPER(t_name) -- this gives us the upper case version of t_name
, UPPER(t_category) -- this gives us the upper case version of t_category
FROM technologies

Which returns:

t_namet_category
ORACLERDB
MONGONOSQL
MYSQLRDB
POSTGRESQLRDB
PYTHONLANGUAGE
PHPLANGUAGE

Now, if we want our uniformity to be in lower case instead of upper case, we merely change our SQL to say:

SELECT
  LOWER(t_name)
, LOWER(t_category)
FROM technologies

Which yields the following set of records:

t_namet_category
oraclerdb
mongonosql
mysqlrdb
postgresqlrdb
pythonlanguage
phplanguage

Now for just a bit on concatenation, so we can show you a very practical way these functions are often used. I say “a bit of concatenation,” because that is an article on its own and I want to keep this article simple and to the point. Same goes with our use of the LEFT(), RIGHT(), and LEN() functions used in some of the following examples. Note the || operator we have introduced in the following SQL. Before we dive in, a small, quick example of the use of this concat operator:

SELECT 'Hello ' || 'World!';

The above SQL yields: Hello World!. Easy, right?

Ready to get back on track with the more complex example of our use case?

Here’s a reminder of the raw contents of our table called “technologies”:

id_techt_namet_category
0OracleRDB
1MongoNoSQL
2MySQLRDB
3PostgreSQLRDB
4PythonLanguage
5PHPLanguage

We next build the following SQL query view:

SELECT
  UPPER(LEFT(t_name,1))
  || LOWER(RIGHT(t_name,LEN(t_name)-1))
  -- LEN(item) returns the length of our string, which in this case is the value stored in t_name.
  -- RIGHT(item, length) returns the length number of characters of item, starting from the right.

  || ' - '
  -- This '||' symbol concatenates. Here we add a space, then dash, then another space to the value we are building.

  || UPPER(LEFT(t_category,1))
  || LOWER(RIGHT(t_category,LEN(t_category)-1))
  -- Here we've done the same thing to t_category that we did to t_name above.
FROM technologies

Which returns this recordset:

t_namet_category
OracleRdb
MongoNosql
MysqlRdb
PostgresqlRdb
PythonLanguage
PhpLanguage

Shortcut

We can further simplify our use case by using the INITCAP() 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 highly useful.

SELECT
  INITCAP(t_name)
  || ' - '
  || INITCAP(t_category)
FROM technologies

Finally, a quick look at another way to do concatenation, using PostgreSQL’s CONCAT function:

SELECT
  CONCAT (
  INITCAP(t_name)
  , ' - '
  , INITCAP(t_category)
  ) AS t_name_tech_combined
FROM technologies

Notes:

  • CONCAT() appeared in PostGres version 9.1.
  • There is CONCAT_WS(), which has nother features and we’ll discuss that in our future article on concatenation.
  • Look for our future articles on LEFT, RIGHT, and string concatenation.

Conclusion

In this article we learned how to use the LOWER() and UPPER() functions in PostgreSQL. We also explored some real world examples. Finally, we touched on a few other related functions such as LEFT(), RIGHT(), LEN(), and CONCAT().

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.