String Functions: CONCAT, UPPER, LOWER, TRIM, REPLACE

Player names arrive from scorers, websites and Excel sheets — with extra spaces, random capitals and mixed formats. String functions let you clean and reshape this text inside the query. They do three jobs:

This topic covers manipulation; the next topic covers calculation and extraction.

Definition

A string function is a single-row function whose input is text (a string, e.g. VARCHAR) and which returns a modified string or information about that string.

Syntax

CONCAT(string1, string2 [, string3 ...])
UPPER(string)
LOWER(string)
TRIM(string)            -- also: LTRIM(string), RTRIM(string)
REPLACE(string, old_value, new_value)

CONCAT — combine strings

CONCAT('Virat', ' ', 'Kohli') → 'Virat Kohli'. The space ' ' is its own argument — without it you get 'ViratKohli'.

Our player_profiles table (the PDF's chapter-7 "players" table) has separate first_name and last_name columns:

SELECT
    CONCAT(first_name, ' ', last_name) AS full_name,
    UPPER(team)                        AS team_u,
    LEFT(last_name, 3)                 AS short
FROM player_profiles;

How it works: each row is processed alone — the functions build three new columns per row. David Warner has no team (NULL), and UPPER(NULL) is still NULL: almost every function returns NULL when its input is NULL.

Note: CONCAT is the exception — it treats a NULL argument as an empty string. CONCAT(name, ' (', team, ')') gives 'David Warner ()', not NULL.

UPPER & LOWER — consistent case

UPPER('Virat') → 'VIRAT', LOWER('VIRAT') → 'virat'. Applying the function to text that is already in that case changes nothing. They are perfect for making text consistent before comparing or grouping: 'rcb', 'Rcb' and 'RCB' all become 'RCB' after UPPER.

TRIM — remove extra spaces

'Dhoni', ' Dhoni', 'Dhoni ' and ' Dhoni ' all become 'Dhoni' after TRIM. Use LTRIM to clean only the left (leading) side and RTRIM for only the right (trailing) side.

SELECT
    CONCAT('[', TRIM('  Dhoni  '), ']')  AS trimmed,
    CONCAT('[', LTRIM('  Dhoni  '), ']') AS left_trim,
    CONCAT('[', RTRIM('  Dhoni  '), ']') AS right_trim;

The square brackets are only there so you can see the spaces.

REPLACE — swap or remove

SELECT
    REPLACE('2025-08-20', '-', '/') AS swapped,
    REPLACE('2025-08-20', '-', '')  AS removed;

REPLACE is not only for replacing — with '' (nothing) as the new value it removes characters.

Example — build e-mail handles

SELECT
    name,
    LOWER(REPLACE(name, ' ', '.'))                    AS handle,
    CONCAT(LOWER(REPLACE(name, ' ', '.')), '@ipl.com') AS email
FROM players;

How it works: REPLACE swaps the space for a dot, LOWER makes it lowercase, and CONCAT glues on the domain — three nested functions, evaluated inside-out.

Common mistakes

• ❌ CONCAT(first_name, last_name) → 'ViratKohli'. ✅ Add the separator: CONCAT(first_name, ' ', last_name).
• ❌ Thinking TRIM removes spaces in the middle ('MS Dhoni' keeps both spaces). ✅ Use REPLACE(x, ' ', ' ') for inner spaces.
• ❌ Comparing dirty text directly: WHERE team = 'RCB' misses ' rcb '. ✅ WHERE UPPER(TRIM(team)) = 'RCB'.

SQL Server vs MySQL / PostgreSQL

Note: In SQL Server you can also write first_name + ' ' + last_name, but if any part is NULL the whole result becomes NULL — another reason to prefer CONCAT. The browser playground does not support + for strings, so use CONCAT there.

Interview tip

Q: What does CONCAT('Team: ', NULL) return, and how is that different from 'Team: ' + NULL? CONCAT treats NULL as an empty string, so it returns 'Team: '. The + operator propagates NULL, so 'Team: ' + NULL returns NULL.