String Functions (CONCAT, SUBSTRING, TRIM)

Real-world text is messy: names split across columns, emails that need a username extracted, codes padded with zeros, fields with stray whitespace. SQL's string functions let you clean, reshape, and combine text right inside your queries — no application code required.

Learn String Functions (CONCAT, SUBSTRING, TRIM) in our free SQL course — a beginner-friendly interactive lesson with runnable queries, a practice exercise…

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

You'll learn to join text with CONCAT , slice it with SUBSTRING , change case with UPPER / LOWER , scrub whitespace with TRIM , swap text with REPLACE , and locate substrings with POSITION .

What You'll Learn

Our Sample Table: users

Split names, mixed-case emails, and a couple of product SKUs we'll slice apart.

1. CONCAT — Joining Text

CONCAT stitches values together into one string. The most common use is building a full name from separate first/last columns. The ANSI-standard alternative is the || operator.

CONCAT is threading beads onto a string: each argument is a bead, and you can slip a separator bead (like a space) between them.

2. LENGTH, UPPER & LOWER

LENGTH returns the number of characters, while UPPER and LOWER change case. Case functions are essential for case-insensitive comparisons: normalize both sides with LOWER() before matching.

3. SUBSTRING — Slicing Text

SUBSTRING(text, start, length) extracts part of a string. The crucial gotcha: positions are 1-based , so the first character is position 1, not 0. Omit the length to take everything from start to the end.

Your Turn: build a label

Fill in the function that joins multiple strings into one.

4. TRIM, REPLACE & POSITION

TRIM strips surrounding whitespace (or a character you name), REPLACE swaps every occurrence of one substring for another, and POSITION (a.k.a. INSTR in some databases) tells you where a substring starts. Together they handle most data-cleaning chores.

🧩 Reorder Challenge

Unscramble these lines into a query that produces an uppercase 3-letter prefix from each sku and sorts by it.

Why: the functions nest inside-out — SUBSTRING(sku, 1, 3) grabs the first three characters (1-based!), then UPPER(...) capitalizes the result. That expression defines the prefix alias in SELECT, and because ORDER BY runs after SELECT, it can sort by that alias directly.

🧠 Quick Recall

'data' — starting at position 1 (the 'd'), taking 4 characters. Remember SQL is 1-based.

2 — TRIM strips the surrounding spaces, leaving 'hi', which has 2 characters.

'abc' — every dash is replaced with an empty string, removing all three separators.

Common Errors (and the fix)

• 0-based assumption: SUBSTRING(s, 0, 3) behaves unexpectedly. Positions start at 1 — use SUBSTRING(s, 1, 3) .
• CONCAT and NULL: in some databases a single NULL argument makes the whole result NULL. Wrap nullable values: CONCAT(a, COALESCE(b, '')) or use CONCAT_WS .
• Dialect drift: LENGTH vs LEN , POSITION vs INSTR , SUBSTRING vs SUBSTR . Check your database's function names.
• Case-insensitive search breaking indexes: WHERE LOWER(col) = '...' may skip a plain index. Use a functional index or case-insensitive collation on hot paths.

📘 Quick Reference

Function

Purpose

Frequently Asked Questions

Mini-Challenge: Email → Username

Combine SUBSTRING, POSITION, LOWER and TRIM to turn an email into a clean username. Expected shape is in the comments.

🎉 Lesson Complete

• ✅ CONCAT (or || ) joins values into one string
• ✅ LENGTH , UPPER , LOWER measure and re-case text
• ✅ SUBSTRING slices text — and positions are 1-based
• ✅ TRIM , REPLACE , POSITION clean and reshape data
• ✅ Function names vary by database — check your dialect
• ✅ Next: work with dates and times — calendars, ranges, and durations

Practice quiz

Continue this course

• Previous: Checkpoint: Querying Data
• Next: Date & Time Functions

Related lessons

• SELECT
• aliases