String Functions

Objectives

By the end of this lesson, you should be able to:

• Join text together with || and CONCAT()
• Change case with UPPER() and LOWER()
• Measure and extract parts of a string with LENGTH() and SUBSTRING()
• Clean up text with TRIM() and REPLACE()

💡 Why this matters: Stored text rarely comes out in exactly the shape a report needs. Combining a first and last name into one column, standardizing capitalization, or pulling out part of an email address are all things PostgreSQL can do inside the query itself.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database, using the same employees table as the previous lesson.

Joining Text: || and CONCAT()

SELECT first_name || ' ' || last_name AS full_name
FROM employees
WHERE first_name = 'Erin';

 full_name
---------------
 Erin Castillo

|| is the standard SQL string concatenation operator, it joins any number of text values (and literals, like the space here) end to end. CONCAT() does the same thing as a function call:

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees
WHERE first_name = 'Erin';

 full_name
---------------
 Erin Castillo

Both produce identical results here. || is more common in PostgreSQL code, CONCAT() has one advantage: it silently skips NULL arguments instead of the whole expression becoming NULL, which || does if any piece is NULL.

Changing Case: UPPER() and LOWER()

SELECT UPPER(first_name) AS upper_name, LOWER(last_name) AS lower_name
FROM employees
WHERE first_name = 'Maya';

 upper_name | lower_name
------------+------------
 MAYA       | fischer

UPPER() and LOWER() do exactly what they say. Common uses include normalizing text before comparing it (WHERE LOWER(email) = LOWER('Erin.Castillo@Example.com')) or formatting for display.

Measuring and Extracting: LENGTH() and SUBSTRING()

SELECT first_name, LENGTH(first_name) AS name_length
FROM employees
WHERE first_name = 'Alexis';

 first_name | name_length
------------+-------------
 Alexis     |           6

LENGTH() counts characters. SUBSTRING() pulls out part of a string, starting position and length:

SELECT email, SUBSTRING(email FROM 1 FOR 4) AS first_four
FROM employees
WHERE first_name = 'Devon';

           email           | first_four
----------------------------+------------
 devon.ellery@example.com  | devo

SUBSTRING(email FROM 1 FOR 4) starts at character 1 and takes 4 characters. The FROM ... FOR ... syntax is the SQL standard form, PostgreSQL also accepts a shorter comma form, SUBSTRING(email, 1, 4), which does the same thing.

Cleaning Text: TRIM() and REPLACE()

SELECT TRIM('   padded   ') AS trimmed;

 trimmed
---------
 padded

TRIM() removes leading and trailing whitespace by default (spaces from the middle are untouched). It’s the standard defense against text that has stray whitespace from user input or an import.

SELECT REPLACE('2023-08-30', '-', '/') AS replaced;

 replaced
------------
 2023/08/30

REPLACE(text, from, to) swaps every occurrence of one substring for another. Here, every - becomes a /.

Try It

1. Write a query that shows every employee’s full name as "Last, First" (e.g. "Castillo, Erin") using ||.
2. Write a query that shows every employee’s email in all uppercase.
3. Write a query that shows the first 3 characters of every employee’s last_name.
4. Write a query using REPLACE() that turns every @example.com in the email column into @company.org (don’t worry about updating the table, just show the result).

Recap

• || and CONCAT() join text together, CONCAT() skips NULL values instead of propagating them.
• UPPER() and LOWER() change case, useful for display and case-insensitive comparisons.
• LENGTH() counts characters, SUBSTRING(text FROM start FOR length) extracts part of a string.
• TRIM() removes leading/trailing whitespace, REPLACE(text, from, to) swaps substrings.

Next lesson: numeric functions, rounding, absolute value, and other calculations on numbers.