String Functions

Intermediate

⏱️ 10 mins read

Cleans up employees (Topic 1) and customers (Topic 6) — no new tables, but Maike's NULL city is about to matter.

What You'll Learn

SQL has built-in string functions for data cleaning and transformation. Common ones: CONCAT (join strings), UPPER/LOWER (change case), LENGTH (character count), SUBSTRING/SUBSTR (extract part of string), TRIM (remove whitespace), REPLACE (substitute text), LEFT/RIGHT (extract from ends).

Syntax

CONCAT(s1, s2)
UPPER(s) / LOWER(s)
LENGTH(s)
SUBSTRING(s, start, length)
TRIM(s) / LTRIM(s) / RTRIM(s)
REPLACE(s, old, new)
LEFT(s, n) / RIGHT(s, n)

Example

-- Shape names from the employees table
SELECT
  name,
  UPPER(name)             AS name_upper,
  LENGTH(name)            AS name_length,
  LEFT(name, 1)           AS initial,
  REPLACE(name, 'a', '@') AS leet_name
FROM employees;

-- Split 'karl@example.com' into local part and domain
SELECT
  name,
  email,
  SUBSTRING(email, 1, POSITION('@' IN email) - 1) AS local_part,
  SUBSTRING(email, POSITION('@' IN email) + 1)    AS domain
FROM customers;

Beyond the Basics

Data in play — reference tables for this topic
-- employees (from Topic 1) — name column
-- Ada, Grace, Alan, Edsger, Barbara

-- customers (from Topic 6)
-- name  | email             | city   | country
-- Karl  | karl@example.com  | Berlin | Germany
-- Ines  | ines@example.com  | Berlin | Germany
-- Omar  | omar@example.com  | Berlin | Austria
-- Sofia | sofia@example.com | London | UK
-- Maike | maike@example.com | NULL   | NULL

String functions in WHERE: the sargability trap, part two

Topic 3 showed YEAR(hire_date) killing an index on dates. Strings have the same disease: WHERE LOWER(email) = 'karl@example.com' calls LOWER on every row and ignores any index on email. Same fix, same shape — put the transformation on the constant, or index the expression itself.

-- Not sargable: function per row, index on email unusable
SELECT * FROM customers WHERE LOWER(email) = 'karl@example.com';

-- Sargable: compare against a normalized constant instead
SELECT * FROM customers WHERE email = 'karl@example.com';

-- If case-insensitivity is genuinely required: functional index (PostgreSQL)
CREATE INDEX idx_cust_email_lower ON customers (LOWER(email));
Any function wrapped around an indexed column in WHERE — string or date — quietly downgrades the query to a full scan.

NULL is contagious when building strings

Concatenate anything with NULL and standard SQL returns NULL — one missing piece blanks the whole string. Maike, our customer with no city, proves it: CONCAT(name, ' — ', city) quietly deletes her from every formatted report. MySQL's CONCAT behaves the same; SQL Server's CONCAT is the outlier, treating NULL as empty.

SELECT
  name,
  CONCAT(name, ' — ', city)              AS label,
  CONCAT(name, ' — ', COALESCE(city, '?')) AS label_safe
FROM customers;
-- Karl  → 'Karl — Berlin'        | 'Karl — Berlin'
-- Maike → NULL  ← she vanished   | 'Maike — ?'
Before formatting output, ask where the NULLs are: one COALESCE per nullable input, or your report silently loses rows.

Parse without regex: POSITION + SUBSTRING, and the 0 edge case

Splitting an email needs no regex: find the '@' with POSITION, slice with SUBSTRING. But POSITION returns 0 when the needle is missing — and SUBSTRING(email, 0) is not an error, it is garbage: everything from a fictional position before the string. Malformed input (an email with no '@') quietly produces broken output.

SELECT
  email,
  POSITION('@' IN email)                          AS at_pos,
  SUBSTRING(email, POSITION('@' IN email) + 1)    AS domain
FROM customers;
-- All five rows work... but feed in 'not-an-email' and at_pos = 0,
-- so SUBSTRING(..., 1) returns the WHOLE string as the 'domain'

-- Defensive version:
SELECT email,
  CASE WHEN POSITION('@' IN email) > 0
       THEN SUBSTRING(email, POSITION('@' IN email) + 1)
  END AS domain
FROM customers;
POSITION returning 0 (not NULL, not an error) is the failure mode — guard string splitting with a CASE when the input is user-supplied.

Common Mistakes

Function names differ by database. SUBSTRING vs SUBSTR, CHARINDEX (SQL Server) vs POSITION (MySQL/PostgreSQL) vs INSTR (Oracle). Always check your DB docs. Also, string functions can prevent index usage — avoid them in WHERE clauses on indexed columns.

Interview Tips

Data cleaning questions are common in analytics interviews. Practice extracting domain from email, parsing date from string, cleaning whitespace, and building full names from parts.

Official References

Test Yourself — 6 questions
Self-check · 0/6 answered

1. Why is WHERE LOWER(email) = 'karl@example.com' a performance problem?

2. CONCAT(name, ' — ', city) on Maike (city is NULL) returns…?

3. POSITION('@' IN 'not-an-email') returns…?

4. LENGTH vs CHAR_LENGTH — when do they disagree?

5. What is the difference between '' (empty string) and NULL?

6. Why do function names like SUBSTRING vs SUBSTR matter in interviews?

Practice

Return every employee's name converted to uppercase.

⚡ Solve it in the SQL playground →

Frequently Asked Questions

LENGTH vs CHAR_LENGTH — why do they differ?

LENGTH counts bytes, CHAR_LENGTH counts characters. With UTF-8, 'ä' is one character but two bytes, so names like 'Jürgen' make the two functions disagree. For human-facing strings, CHAR_LENGTH (or CHARACTER_LENGTH) is almost always what you meant.

Does 'abc' TRIM to 'abc' — and what about empty vs NULL?

TRIM removes spaces (and optionally other characters) from both ends, so ' abc ' → 'abc'. But '' (empty string) is not NULL: LENGTH('') is 0, while LENGTH(NULL) is NULL. Cleaning pipelines must handle both separately — trimming never turns an empty string into NULL.

Back to Full Roadmap