Beginner7 min read PostgreSQL

String Functions

Clean and reshape text data with UPPER, LOWER, TRIM, CONCAT, SPLIT_PART, ILIKE, and more — essential for normalising messy real-world data.

UPPER
LOWER
TRIM
CONCAT
SPLIT_PART
ILIKE
strings

Use this when you need to:

Clean up user input — TRIM whitespace, UPPER/LOWER for case normalization
Match without caring about case using ILIKE so "smith" matches "Smith" matches "SMITH"
Parse parts out of an email, URL, or full address with SPLIT_PART
Build human-readable labels for reports with CONCAT — full names, formatted IDs, "$X / month" labels

Real-world text data is messy — names in ALL CAPS, emails with extra spaces, course codes that need parsing, notes fields you need to search. String functions let you clean, reshape, and extract text without touching the underlying data.

Case Conversion — UPPER, LOWER, INITCAP

SELECT UPPER('john doe');        -- 'JOHN DOE'
SELECT LOWER('JOHN DOE');        -- 'john doe'
SELECT INITCAP('john doe');      -- 'John Doe'  (capitalises each word)

-- Normalise names and emails before they reach a report
SELECT
    INITCAP(first_name)                    AS first_name,
    INITCAP(last_name)                     AS last_name,
    LOWER(TRIM(preferred_email_address))   AS email
FROM ods_person;

TRIM — Remove Whitespace

SELECT TRIM('  hello  ');        -- 'hello'
SELECT LTRIM('  hello  ');       -- 'hello  '  (left only)
SELECT RTRIM('  hello  ');       -- '  hello'  (right only)

-- Always TRIM before comparing user-entered values
SELECT id, first_name, last_name
FROM ods_person
WHERE TRIM(LOWER(preferred_email_address)) = '[email protected]';
-- 0100001 | Jordan | Peña

CONCAT and || Operator

-- CONCAT ignores NULLs; || propagates them.
-- middle_name is NULL for most people: the first column still
-- has a value (with a double space), the second is NULL.
SELECT
    CONCAT(first_name, ' ', middle_name, ' ', last_name) AS with_concat,
    first_name || ' ' || middle_name || ' ' || last_name AS with_pipes
FROM ods_person;

-- Build a display label: "J. Peña"
SELECT LEFT(UPPER(first_name), 1) || '. ' || INITCAP(last_name) AS short_name
FROM ods_person;

SPLIT_PART — Parse Delimited Strings

SPLIT_PART splits a string on a delimiter and returns the nth piece, counting from 1. It is perfect for parsing composite keys, course codes, email domains, or any structured string. In the practice schema, a section id packs three facts into one string: course, term and section number, separated by asterisks.

-- Section ids are stored as 'COURSE*TERM*SECTION', e.g. 'ACCT-101*2024FA*01'
SELECT
    course_sections_id,
    SPLIT_PART(course_sections_id, '*', 1)                  AS course,      -- 'ACCT-101'
    SPLIT_PART(course_sections_id, '*', 2)                  AS term,        -- '2024FA'
    SPLIT_PART(course_sections_id, '*', 3)                  AS section_no,  -- '01'
    SPLIT_PART(SPLIT_PART(course_sections_id, '*', 1), '-', 1) AS subject   -- 'ACCT'
FROM ods_course_sections;

-- Extract email domain
SELECT
    preferred_email_address,
    SPLIT_PART(preferred_email_address, '@', 2) AS domain   -- 'queryu.edu'
FROM ods_person;

ILIKE — Case-Insensitive Search

-- LIKE is case-sensitive; ILIKE is not (PostgreSQL-specific)
SELECT courses_id, crs_title
FROM ods_courses
WHERE crs_title ILIKE '%introduction%';   -- matches 'Introduction', 'INTRODUCTION', etc.

-- LIKE with a lowercase pattern finds nothing here, because every title
-- is stored as 'Introduction to ...'. ILIKE finds all 11.

-- Find all computer science courses regardless of how the subject is typed
SELECT courses_id, crs_title
FROM ods_courses
WHERE crs_subject ILIKE 'csci';

ILIKE is PostgreSQL-specific. Standard SQL uses LOWER(column) LIKE LOWER(pattern) to achieve the same result across databases.

Practice challenge

Email addresses live in ods_person.preferred_email_address. Treat the column the way you would treat anything typed into a web form: clean it before you compare it. Return id, a cleaned email (lowercase, trimmed), and username (the part before the @). Filter to addresses ending in @queryu.edu only.

Solution

SELECT
    id,
    LOWER(TRIM(preferred_email_address))                   AS email,
    SPLIT_PART(LOWER(TRIM(preferred_email_address)), '@', 1) AS username
FROM ods_person
WHERE LOWER(TRIM(preferred_email_address)) LIKE '%@queryu.edu'
ORDER BY username;

Practise this on real university data

Write real PostgreSQL in your browser and get it graded on the rows it returns. No signup and nothing to install for your first query.

Run your first query Browse practice problems

See all 21 lessons