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 .

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

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.

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.

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.

Fill in the function that joins multiple strings into one.

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.

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.

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

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

Practice quiz

What does CONCAT do?

  • Counts characters
  • Slices a substring
  • Joins values into one string
  • Changes case

Answer: Joins values into one string. CONCAT stitches values together into one string; the ANSI-standard equivalent is the || operator.

Which is the ANSI-standard operator for joining strings?

  • ||
  • +
  • &
  • .

Answer: ||. The || operator is the ANSI standard (Postgres, SQLite, Oracle); CONCAT is the most portable function.

In SQL, what position is the first character of a string for SUBSTRING?

  • 0
  • -1
  • It depends on the row
  • 1

Answer: 1. SQL string positions are 1-based, so SUBSTRING('hello', 1, 1) returns 'h', not 'e'.

What does SUBSTRING('database', 1, 4) return?

  • 'atab'
  • 'data'
  • 'abas'
  • 'datab'

Answer: 'data'. Starting at position 1 (the 'd') and taking 4 characters gives 'data'.

What is LENGTH(TRIM(' hi '))?

  • 2
  • 6
  • 4
  • 0

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

What does REPLACE('a-b-c', '-', '') produce?

  • 'a-b-c'
  • 'a b c'
  • 'abc'
  • '-abc'

Answer: 'abc'. REPLACE swaps every occurrence of the dash with an empty string, removing all three separators.

What do UPPER and LOWER do, and why are they used for matching?

  • Sort rows; for ordering
  • Change case; normalize both sides for case-insensitive comparison
  • Count characters; for length checks
  • Trim spaces; for cleaning

Answer: Change case; normalize both sides for case-insensitive comparison. UPPER/LOWER change case; normalizing both sides with LOWER() enables case-insensitive matching (but can skip a plain index).

What does POSITION return when the substring is absent?

  • NULL
  • -1
  • An error
  • 0

Answer: 0. POSITION (a.k.a. INSTR) is 1-based and returns 0 when the substring does not appear.

In MySQL, what can happen to CONCAT if one argument is NULL?

  • It ignores NULLs always
  • The whole result can become NULL
  • It throws an error
  • It returns 0

Answer: The whole result can become NULL. In MySQL CONCAT returns NULL if any argument is NULL; wrap nullable values in COALESCE or use CONCAT_WS.

Why can SUBSTRING(s, 0, 3) behave unexpectedly?

  • 0 is a reserved word
  • SUBSTRING needs four arguments
  • Positions start at 1, not 0, so 0 is out of the normal range
  • It only works on numbers

Answer: Positions start at 1, not 0, so 0 is out of the normal range. SQL string indexing is 1-based; use SUBSTRING(s, 1, 3) to take the first three characters.