Postgres String Case Lower and Upper in SQL
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
UPPERstring 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.
- Knowledge of the use of the most basic SQL (PL/SQL) statements, including
- Understanding of what a String is and what concatenation means.
What are the LOWER and UPPER functions?
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?
t_newString := LOWER(t_oldString);
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:
SELECT LOWER(t_name) FROM technologies
The above query will return the following results:
Let’s do the same for the UPPER function:
SELECT UPPER(t_name) FROM technologies
Returns the following recordset:
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:
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 capitalization (case), so we let’s use our trusty
UPPER() function to force uppercase:
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
Now, if we want our uniformity to be in lower case instead of upper case, we merely change our SQL to say:
Which yields the following set of records:
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”:
We next build the following SQL query view:
-- 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.
-- Here we've done the same thing to t_category that we did to t_name above.
Which returns this recordset:
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
LOWER, which will at times be highly useful.
|| ' - '
Finally, a quick look at another way to do concatenation, using PostgreSQL’s
, ' - '
) AS t_name_tech_combined
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
RIGHT, and string concatenation.
In this article we learned how to use the
UPPER() functions in PostgreSQL. We also explored some real world examples. Finally, we touched on a few other related functions such as
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started