String Functions

Clean, cut, join and match text: LENGTH, SUBSTR, UPPER, TRIM, REPLACE and pattern matching.

• Intermediate
• 13 min read
• 3 objectives

Before this lessonLesson 7: CASE and Conditional Logic

Real columns are messy: trailing spaces, inconsistent capitals, a full name where you wanted a first name. These functions are how you fix that in the query instead of in a spreadsheet.

Measuring and cutting

LENGTH counts characters. SUBSTR(text, start, count) cuts a piece out, and SQL counts from 1, not 0 — a reliable source of off-by-one bugs.

SELECT name,
       LENGTH(name) AS len,
       SUBSTR(name, 1, 1) AS initial,
       SUBSTR(name, 2) AS rest
FROM customers;

name  | len | initial | rest
------+----+--------+-----
Ada   | 3   | A       | da
Linus | 5   | L       | inus
Grace | 5   | G       | race

Joining text

The standard concatenation operator is ||. Anything concatenated with NULL becomes NULL, so wrap nullable columns in COALESCE first.

SELECT name || ' (' || country || ')' AS label,
       name || ' → ' || COALESCE(phone, 'no phone') AS contact
FROM customers;

label      | contact
-----------+------------------------
Ada (UK)   | Ada → +44 20 7946 0958
Linus (FI) | Linus → no phone
Grace (US) | Grace → +1 202 555 0143

Changing case, and the classic name fix

Combining UPPER, LOWER and SUBSTR gives you capitalisation — LeetCode 1667 Fix Names in a Table is exactly this query.

SELECT id,
       UPPER(SUBSTR(email, 1, 1)) || LOWER(SUBSTR(email, 2)) AS fixed
FROM person
ORDER BY id;

id | fixed
---+-------------------
1  | Ada@example.com
2  | Linus@example.com
3  | Ada@example.com
4  | Grace@example.com

Trimming and replacing

Row 4 of person has a trailing space and shouty capitals — the kind of value that silently breaks a join. TRIM and LOWER normalize it.

SELECT id,
       '[' || email || ']' AS raw,
       '[' || LOWER(TRIM(email)) || ']' AS clean
FROM person;

id | raw                  | clean
---+---------------------+--------------------
1  | [ada@example.com]    | [ada@example.com]
2  | [linus@example.com]  | [linus@example.com]
3  | [ada@example.com]    | [ada@example.com]
4  | [GRACE@example.com ] | [grace@example.com]

SELECT REPLACE('2024-01-31', '-', '/') AS slashes,
       REPLACE(email, '@example.com', '') AS handle
FROM customers;

slashes    | handle
-----------+-------
2024/01/31 | ada
2024/01/31 | grace
2024/01/31 | linus

Finding text inside text

INSTR(haystack, needle) returns the 1-based position, or 0 when it is absent. Combined with SUBSTR it splits a value on a separator.

SELECT email,
       INSTR(email, '@') AS at_position,
       SUBSTR(email, 1, INSTR(email, '@') - 1) AS local_part,
       SUBSTR(email, INSTR(email, '@') + 1) AS domain
FROM customers;

email             | at_position | local_part | domain
------------------+------------+-----------+------------
ada@example.com   | 4           | ada        | example.com
grace@example.com | 6           | grace      | example.com
linus@example.com | 6           | linus      | example.com

Pattern matching

• LIKE uses % for any run of characters and _ for exactly one.
• LIKE is case-insensitive for ASCII in SQLite and MySQL, but case-sensitive in Postgres (which offers ILIKE).
• A leading % cannot use an index — see the indexes lesson.

SELECT name FROM customers WHERE email LIKE '%@example.com';
SELECT name FROM customers WHERE name LIKE '_da';

name
------
Ada
Linus
Grace

name
-----
Ada

Grouping text back together

The inverse of splitting: collapse many rows into one delimited string.

SELECT department, GROUP_CONCAT(name, ', ') AS team
FROM employees
GROUP BY department;

department  | team
------------+------------------------
Design      | Margaret, Alan, Barbara
Engineering | Grace, Ada, Linus

Key takeaways

• String positions are 1-based.
• Concatenation with NULL yields NULL; guard with COALESCE.
• TRIM + LOWER is the standard normalisation before comparing.
• INSTR with SUBSTR splits a value on a separator.

-- Write your solution here