String Functions

Last Updated: June 12, 2026 • 18 min read

SQL provides a rich set of string functions for manipulating text data directly within queries. Mastering these functions eliminates the need to process strings in application code and enables powerful data transformations at the database level.

1. Introduction

String functions transform text data directly inside SQL queries — no need to pull data into application code. They cover case conversion (UPPER, LOWER, INITCAP), concatenation (||, CONCAT, CONCAT_WS), measurement (LENGTH), extraction (SUBSTRING, LEFT, RIGHT), trimming (TRIM), and replacement (REPLACE, LPAD, RPAD). These are essential for data cleaning, report formatting, and ETL pipelines.

2. Why It Matters

Data cleaning: User input is messy — extra spaces, inconsistent casing, special characters. String functions normalize data at insert or query time.
Report formatting: Formatted IDs (LPAD), proper-case names (INITCAP), and concatenated labels (CONCAT_WS) make reports readable.
Extraction logic: Pulling email domains, area codes, file extensions, or URL paths from composite strings avoids brittle regex in application code.
Performance: Doing transformations in SQL avoids round-tripping data to application code and back.

3. Real-World Analogy

String functions are like a word processor's find-and-replace, formatting, and spell-check tools — but operating on millions of rows at once. UPPER/LOWER standardize casing like "make all text uppercase." SUBSTRING is like highlighting and copying a portion of text. TRIM removes accidental leading/trailing spaces. CONCAT stitches fields together like mail merge. The database does all this in a single pass, without exporting to a spreadsheet.

4. How It Works

Core string function categories:

Category	Functions	Example
Case	UPPER, LOWER, INITCAP	INITCAP('john doe') → 'John Doe'
Concat	\|\|, CONCAT, CONCAT_WS	CONCAT('a', ' ', 'b') → 'a b'
Measure	LENGTH	LENGTH('hello') → 5
Extract	SUBSTRING, LEFT, RIGHT, POSITION	SUBSTRING('hello', 2, 3) → 'ell'
Trim/Pad	TRIM, LTRIM, LPAD, RPAD	LPAD('42', 5, '0') → '00042'
Replace	REPLACE, REVERSE	REPLACE('hello', 'l', 'L') → 'heLLo'

NULL handling difference: The || operator returns NULL if any operand is NULL. CONCAT() treats NULL as empty string — usually the desired behavior.

5. Internal Architecture

How the engine processes string functions:

6. Visual Explanation

7. Practical Example

8. Common Mistakes

Using || with nullable columns: If last_name is NULL, then first_name || ' ' || last_name returns NULL. Use CONCAT(first_name, ' ', last_name) instead.
LENGTH vs OCTET_LENGTH: LENGTH counts characters, OCTET_LENGTH counts bytes. For emojis and multi-byte UTF-8, these differ: LENGTH('') = 1 but OCTET_LENGTH('') = 4.
SUBSTRING is 1-indexed: SUBSTRING(str, 0, 3) does NOT return the first 3 chars. Use SUBSTRING(str, 1, 3).

Interview Insight

Extracting email domains, phone area codes, or file extensions is a classic interview task. Know the combo: SUBSTRING(email FROM POSITION('@' IN email) + 1).

Common Pitfall

Using || for concatenation when any column might be NULL — the entire result becomes NULL. Always use CONCAT() or COALESCE the operands.

9. Quick Quiz

Q1: What does CONCAT('Hello', NULL, 'World') return?

A) NULL B) 'HelloWorld' C) 'Hello World'

Answer: B — CONCAT treats NULL as empty string, so the result is 'HelloWorld'.

Q2: What does SUBSTRING('PostgreSQL', 1, 4) return?

A) 'Post' B) 'Pos' C) 'Postg'

Answer: A — Starts at position 1, extracts 4 characters: 'Post'.

10. Scenario-Based Challenge

Clean and Normalize a Messy Contacts Table

Your contacts table has: names with extra spaces (" John Doe "), emails in mixed case ("John@GMAIL.COM"), phone numbers with dashes and spaces ("555-123 4567"), and some NULL fields. Write a query that produces clean, standardized output: trimmed proper-case names, lowercase emails, digits-only phone numbers, and 'N/A' for NULL fields using COALESCE.

11. Debugging Exercise

This query should display full names but shows NULL for some users. Why?

12. Interview Questions

Q: How do you extract the domain from an email column?

A: Use SUBSTRING(email FROM POSITION('@' IN email) + 1). In PostgreSQL, you can also use SPLIT_PART(email, '@', 2). For grouping/counting by domain, combine with GROUP BY.

Q: What's the difference between || and CONCAT?

A: || is the SQL standard operator — returns NULL if any operand is NULL. CONCAT() is a function that treats NULL as empty string. CONCAT_WS(sep, ...) adds a separator and skips NULLs. For production code with nullable columns, CONCAT is safer.

Q: How do you aggregate strings from multiple rows into one?

A: Use STRING_AGG(column, separator ORDER BY ...) in PostgreSQL, GROUP_CONCAT in MySQL, or STRING_AGG in SQL Server. This collapses multiple rows into a single comma-separated string per group.

13. Production Considerations

Expression indexes: If you frequently filter by LOWER(email), create an expression index: CREATE INDEX ON users (LOWER(email)).
PostgreSQL regex: For complex patterns beyond LIKE, use REGEXP_REPLACE and REGEXP_MATCHES — but they're slower than simple string functions.
Encoding awareness: LENGTH counts characters not bytes. For multi-byte UTF-8 (emojis, CJK), LENGTH and OCTET_LENGTH differ. Plan storage and display accordingly.
Computed columns: For frequently used transformations, consider generated columns: normalized_email TEXT GENERATED ALWAYS AS (LOWER(TRIM(email))) STORED.