Text & Pattern Matching

Text functions are how SQL turns stored strings into usable labels, search keys, and cleanup columns. In Postgres, you can change casing, trim whitespace, slice text, glue pieces together, and match patterns without changing the stored rows.

This section follows joins on purpose: once the right rows are on screen, functions shape the values inside each row.

The everyday text toolkit

A function takes an input value and returns a value. Text functions work row by row, so each menu item gets its own transformed result:

SELECT name,
       UPPER(category) AS category_badge,
       LOWER(name) AS search_name,
       LENGTH(name) AS characters,
       SUBSTRING(name FROM 1 FOR 4) AS first_four,
       SPLIT_PART(name, ' ', 1) AS first_word
FROM menu_items
WHERE id IN (2, 8, 10)
ORDER BY id;

      name       | category_badge |   search_name   | characters | first_four | first_word
-----------------+----------------+-----------------+------------+------------+------------
 Cappuccino      | COFFEE         | cappuccino      |         10 | Capp       | Cappuccino
 Cinnamon Roll   | PASTRY         | cinnamon roll   |         13 | Cinn       | Cinnamon
 Sparkling Water | DRINKS         | sparkling water |         15 | Spar       | Sparkling
(3 rows)

UPPER and LOWER are for display and matching habits, not for changing the table. LENGTH counts characters in the text value, including spaces. SUBSTRING(name FROM 1 FOR 4) starts at the first character and takes four. Postgres's SPLIT_PART(name, ' ', 1) splits on spaces and returns the first piece, which is why Cinnamon Roll becomes Cinnamon.

The important habit is to read functions inside out: the column value goes in, the shaped value comes out, and AS gives the grid a name worth reading.

Concatenation: operator and function shapes

Concatenation means gluing text together. You already previewed the standard/Postgres operator, ||; Postgres also has the function-shaped CONCAT():

SELECT name || ' (' || category || ')' AS operator_label,
       CONCAT(name, ' (', category, ')') AS function_label
FROM menu_items
WHERE id IN (1, 10)
ORDER BY id;

      operator_label      |      function_label
--------------------------+--------------------------
 Espresso (coffee)        | Espresso (coffee)
 Sparkling Water (drinks) | Sparkling Water (drinks)
(2 rows)

Both are useful reading skills. In this course, || is the normal Postgres spelling when you are building one label from several pieces. CONCAT() is common in cross-engine examples because it looks like a regular function call; specific database accents differ, and Section 20 gives that full map.

LIKE mastery

LIKE is for simple text patterns. The two wildcards do most of the work: % means "any number of characters," and _ means "exactly one character." Put % at the end for "starts with," at the beginning for "ends with," and on both sides for "contains."

SELECT name,
       name LIKE 'C%' AS starts_with_c,
       name LIKE 'Earl Gr_y' AS one_letter_gap,
       name ILIKE '%bread%' AS case_blind_bread
FROM menu_items
WHERE name LIKE 'C%'
   OR name LIKE 'Earl Gr_y'
   OR name ILIKE '%bread%'
ORDER BY name;

     name      | starts_with_c | one_letter_gap | case_blind_bread
---------------+---------------+----------------+------------------
 Banana Bread  | f             | f              | t
 Cappuccino    | t             | f              | f
 Cinnamon Roll | t             | f              | f
 Cold Brew     | t             | f              | f
 Croissant     | t             | f              | f
 Earl Grey     | f             | t              | f
(6 rows)

C% finds every name that starts with capital C. Earl Gr_y matches Earl Grey because _ fills one character. ILIKE is PostgreSQL's case-blind accent: it is why lowercase %bread% still finds Banana Bread. Treat ILIKE as a Postgres feature, not universal SQL.

Pattern matching is still exact about the pattern language. %bread% does not mean "sounds like bread" or "is bakery-related"; it means those letters appear in that order.

SIMILAR TO and a regex taste

When LIKE is too small, Postgres gives you two stronger tools. SIMILAR TO sits between LIKE and full regular expressions: it keeps % and _, but adds pattern pieces such as alternatives. Regular expression matching uses the ~ operator.

SELECT name,
       name SIMILAR TO '%(Bread|Brew)' AS similar_to,
       name ~ '^C' AS regex_starts_c
FROM menu_items
WHERE name SIMILAR TO '%(Bread|Brew)'
   OR name ~ '^C'
ORDER BY name;

     name      | similar_to | regex_starts_c
---------------+------------+----------------
 Banana Bread  | t          | f
 Cappuccino    | f          | t
 Cinnamon Roll | f          | t
 Cold Brew     | t          | t
 Croissant     | f          | t
(5 rows)

SIMILAR TO '%(Bread|Brew)' reads as "anything, then Bread or Brew." The leading % matters because the whole name still has to match the pattern. name ~ '^C' uses regex syntax: ^ means the start of the string.

This is a function lesson, not a regex reference. Reach for LIKE first when the pattern is simple. Reach for regex when the shape has real structure: starts with one of several prefixes, contains only allowed characters, or needs cleanup more precise than plain REPLACE.

Cleaning messy text

Real text often arrives with extra spaces, inconsistent casing, and stray characters. The safest first move is to shape it in a SELECT so you can inspect the result before any later data-changing lesson teaches how to store the cleanup.

SELECT '[' || raw_label || ']' AS visible_raw,
       TRIM(raw_label) AS trimmed,
       REGEXP_REPLACE(
         REGEXP_REPLACE(
           LOWER(TRIM(REPLACE(raw_label, '-', ' '))),
           '[^a-z ]',
           '',
           'g'
         ),
         '[[:space:]]+',
         ' ',
         'g'
       ) AS cleaned_label
FROM raw_item_labels
ORDER BY id;

    visible_raw    |     trimmed     | cleaned_label
-------------------+-----------------+---------------
 [  espresso!!  ]  | espresso!!      | espresso
 [Cold   Brew]     | Cold   Brew     | cold brew
 [CINNAMON-ROLL]   | CINNAMON-ROLL   | cinnamon roll
 [tea / earl grey] | tea / earl grey | tea earl grey
(4 rows)

The brackets make leading and trailing spaces visible. TRIM removes spaces at the ends. REPLACE(raw_label, '-', ' ') turns the hyphen into a word boundary before punctuation is stripped. LOWER standardizes casing. The first REGEXP_REPLACE removes characters that are not lowercase letters or spaces; the second collapses runs of whitespace to one space.

Nested functions can feel dense because each result feeds the next function. Later, CTEs will let you name each stage. For now, read from the inside outward and keep the output grid visible while you tune the expression.

You can now shape text after the right rows are present: labels, search keys, simple patterns, and cleanup passes. Next, the same idea moves to dates, times, and time zones, where the functions are just as useful and the traps are sharper.