String Functions (CONCAT, SUBSTRING, TRIM)

Reviewed & published by Brayan K

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.

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

• Join text with CONCAT and the || operator
• Measure and re-case text (LENGTH, UPPER, LOWER)
• Slice text with SUBSTRING (1-based!)
• Clean whitespace and pad chars with TRIM
• Swap substrings with REPLACE
• Locate text with POSITION / INSTR

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.

-- engine: postgres — this lesson uses the standard string functions
-- (CONCAT, POSITION … IN, TRIM('0' FROM …)), which SQLite does not have.
-- The three tables these examples query:
CREATE TABLE users (
    id         INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name  TEXT,
    username   TEXT,
    email      TEXT,
    phone      TEXT
);
INSERT INTO users VALUES
    (1, 'Amélie', 'Dubois', 'amelie', 'Amelie@Shop.COM', '555-12-99'),
    (2, 'Lucas',  'Martin', 'lucas',  'LUCAS@mail.io',   '555-40-01');

CREATE TABLE products (
    id           INTEGER PRIMARY KEY,
    sku          TEXT,
    product_name TEXT,
    description  TEXT
);
INSERT INTO products VALUES
    (1, 'ABC-1001', 'Wireless Mouse', 'A quiet two-button mouse'),
    (2, 'XYZ-2042', 'Desk Lamp',      'Warm light, three levels');

CREATE TABLE imports (
    raw_code  TEXT,
    phone     TEXT,
    padded_id TEXT
);
INSERT INTO imports VALUES
    ('  A1 ', '555-12-99', '00042');

-- CONCAT joins values into one string
SELECT
    CONCAT(first_name, ' ', last_name) AS full_name
FROM users;

-- ✅ Expected result:
-- full_name
-- Amélie Dubois
-- Lucas Martin

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.

-- Uses the tables from the first example.
-- LENGTH counts characters; UPPER / LOWER change case
SELECT
    email,
    LENGTH(email)  AS len,
    UPPER(first_name) AS shout,
    LOWER(email)   AS normalized
FROM users;

-- ✅ Expected result:
-- email | len | shout | normalized
-- Amelie@Shop.COM | 15 | AMÉLIE | amelie@shop.com
-- LUCAS@mail.io | 13 | LUCAS | lucas@mail.io

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.

-- Uses the tables from the first example.
-- SUBSTRING(text, start, length) — positions start at 1, not 0!
SELECT
    sku,
    SUBSTRING(sku, 1, 3) AS prefix,   -- first 3 chars
    SUBSTRING(sku, 5)    AS rest      -- from position 5 to the end
FROM products;

-- ✅ Expected result:
-- sku | prefix | rest
-- ABC-1001 | ABC | 1001
-- XYZ-2042 | XYZ | 2042

Your Turn: build a label

Fill in the function that joins multiple strings into one.

-- Uses the tables from the first example.
-- 🎯 YOUR TURN — fill in the function, then "Try it Yourself"
-- Goal: build a "label" column = first_name, a space, then last_name.

SELECT
    ___(first_name, ' ', last_name) AS label   -- 👉 the string-joining function
FROM users;

-- ✅ Expected: "Amélie Dubois", "Lucas Martin", ...

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.

-- Uses the tables from the first example.
-- TRIM removes surrounding spaces; REPLACE swaps text
SELECT
    TRIM(raw_code)                          AS clean_code,
    REPLACE(phone, '-', '')                 AS digits_only,
    TRIM('0' FROM padded_id)                AS unpadded
FROM imports;

-- raw_code='  A1 '   -> 'A1'
-- phone='555-12-99'  -> '5551299'
-- padded_id='00042'  -> '42'

-- ✅ Expected result:
-- clean_code | digits_only | unpadded
-- A1 | 5551299 | 42

-- Uses the tables from the first example.
-- POSITION / INSTR finds where a substring appears (1-based, 0 if absent)
SELECT
    email,
    SUBSTRING(email, 1, POSITION('@' IN email) - 1) AS local_part
FROM users;

-- ✅ Expected result:
-- email | local_part
-- Amelie@Shop.COM | Amelie
-- LUCAS@mail.io | LUCAS

🧩 Reorder Challenge

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

-- SCRAMBLED — arrange into a query that builds a uppercase
-- 3-letter prefix from each product's sku (e.g. 'abc-1001' -> 'ABC').

FROM products
SELECT sku,
UPPER(SUBSTRING(sku, 1, 3)) AS prefix
ORDER BY prefix

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

Predict each result, then reveal.

'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.

Mini-Challenge: Email → Username

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

-- 🎯 MINI-CHALLENGE — uses only this lesson's ideas
-- Build a clean username from each email:
--   1. Take everything BEFORE the @  (SUBSTRING + POSITION)
--   2. Force it to lowercase          (LOWER)
--   3. Remove any stray spaces        (TRIM)
-- Show email and the resulting "username".
--
-- ✅ Expected: e.g. 'amelie@shop.com' -> 'amelie'

-- your query here

🎉 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 — Work with dates — DATEADD, DATEDIFF, EXTRACT, NOW
• Quick reference: SQL cheat sheet › String Functions

Frequently asked questions

How do I join strings — CONCAT or || ?

Both work. CONCAT(a, b, c) is a function available in MySQL, SQL Server and Postgres. The || operator is the ANSI standard (Postgres, SQLite, Oracle). MySQL uses || for logical OR unless PIPES_AS_CONCAT is set, so CONCAT is the most portable choice.

Is SUBSTRING 1-indexed or 0-indexed?

SQL string positions start at 1, not 0. SUBSTRING('hello', 1, 3) returns 'hel'. This trips up programmers used to 0-based languages, so always remember: the first character is position 1.

What's the difference between TRIM, LTRIM and RTRIM?

TRIM removes characters (spaces by default) from both ends, LTRIM from the left only, and RTRIM from the right only. Many databases also let you specify which character to trim, e.g. TRIM('0' FROM '00042') = '42'.

Does CONCAT handle NULL gracefully?

It depends. In MySQL, CONCAT returns NULL if any argument is NULL, while CONCAT_WS skips NULLs. In Postgres, CONCAT treats NULL as an empty string but the || operator returns NULL. Wrap nullable values in COALESCE to be safe and predictable.

How do I do case-insensitive matching with these functions?

Normalize both sides with LOWER() or UPPER(): WHERE LOWER(email) = LOWER(:input). Be aware this can prevent the use of a plain index on the column, so for hot paths consider a functional index or a case-insensitive collation.

Related lessons

• SELECT
• aliases