String Formatting: CONCAT(), UPPER(), LOWER()

Working with text data is an everyday part of database tasks. Imagine you’re a developer for an online store, and you have a users database. You need to display full user names for a welcome banner on the site, like: "Welcome, Otto Art!". Or maybe you need to standardize all email addresses (for example, make them all lowercase). In these cases, string operations are a must.

PostgreSQL gives you some powerful tools for handling text. Today we’ll break down three main tools:

• CONCAT() — joining strings together.
• UPPER() — turning a string into uppercase.
• LOWER() — turning a string into lowercase.

Working with CONCAT()

The CONCAT() function lets you glue several strings into one. It’s super handy if your data is stored in different columns but you want to show them together.

Syntax

CONCAT(string1, string2, ..., stringN)

• string1, string2, stringN — the strings or columns you want to join.
• Returns a single string made up of all the arguments.

Example: working with names

Let’s say we have a students table:

Our task: show full student names in the "FirstName LastName" format.

Query:

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM students;

Result:

Notice the ' ' between the first and last name. That’s a space we added to separate the strings.

How it works

CONCAT() ignores NULL. If one of the arguments is NULL, it just skips it. If you don’t know what NULL is yet, don’t worry — we’ll talk about it a bit later.

An alternative to CONCAT() is the || operator, for example:

SELECT first_name || ' ' || last_name AS full_name FROM students;

Changing string case: UPPER() and LOWER()

Imagine you’re working with email addresses that users entered in all sorts of cases: ivan@example.com, IVAN@EXAMPLE.COM. To standardize, you often need to:

• make text look the same before comparing (like lowercase).
• highlight important words (like UPPERCASE for titles).

UPPER(): text to uppercase

The UPPER() function turns text into all capital letters.

Syntax:

UPPER(string)

• string — a string or the name of a text column.

Example:

The students table now has an email column:

We want to show email addresses in uppercase:

SELECT email, UPPER(email) AS email_upper
FROM students;

Result:

LOWER(): text to lowercase

Similarly, the LOWER() function turns text into all lowercase.

Syntax:

LOWER(string)

Example:

Let’s make all email addresses lowercase:

SELECT email, LOWER(email) AS email_lower
FROM students;

Result:

Practical examples and cases

Let’s put everything we’ve learned together.

Creating a full name in uppercase

Query:

SELECT CONCAT(UPPER(first_name), ' ', UPPER(last_name)) AS full_name_upper
FROM students;

Result:

Here we used the CONCAT() function to join the first and last names, and UPPER() to make the text uppercase.

Example: standardizing email addresses

We want to make all email addresses lowercase:

UPDATE students
SET email = LOWER(email);

Now all emails in the table look the same, no matter what case they were in before.

You’ll learn how the UPDATE operator works in the next lectures.

Preparing a greeting for users

Let’s add a "Welcome" greeting to the full names:

SELECT CONCAT('Welcome, ', first_name, ' ', last_name, '!') AS greeting
FROM students;

Result:

Common mistakes when working with string functions

If you skip the space in CONCAT(), the result might look weird. For example:

SELECT CONCAT(first_name, last_name) AS full_name
FROM students;

The result will be: "OttoArt", "MariaChi". Always add a space where you need it.

Using NULL. If one of the CONCAT() arguments is NULL, the string might end up incomplete. For example:

SELECT CONCAT(first_name, ' ', middle_name, ' ', last_name) AS full_name
FROM students;

If middle_name = NULL, the result will be "Otto NULL Art". To avoid this, you can use the COALESCE() function:

SELECT CONCAT(first_name, ' ', COALESCE(middle_name, ''), ' ', last_name) AS full_name
FROM students;

You’ll learn more about NULL and the COALESCE() function in upcoming lectures :P