String Functions: LEN, LEFT, RIGHT, SUBSTRING

Need the first letter of every player for initials? The last 3 characters of a code? The surname hidden inside a full name? These are calculation (LEN) and extraction (LEFT, RIGHT, SUBSTRING) jobs.

Definition

Character positions: In 'Virat Kohli', V is position 1, the space is position 6 and K is position 7. SQL counts from 1, unlike Python which counts from 0.

Syntax

LEN(value)
LEFT(value, number_of_characters)
RIGHT(value, number_of_characters)
SUBSTRING(value, start_position, length)

Example — the basics on one string

SELECT
    LEN('Virat')                     AS len_virat,
    LEN('MS Dhoni')                  AS len_dhoni,
    LEN(750)                         AS len_number,
    LEN('2025-08-20')                AS len_date,
    LEFT('Virat Kohli', 2)           AS left_2,
    RIGHT('Virat Kohli', 3)          AS right_3,
    SUBSTRING('Virat Kohli', 7, 5)   AS sub_7_5,
    SUBSTRING('Virat Kohli', 1, 5)   AS sub_1_5;

How it works: 'MS Dhoni' is 8 because the space counts. 750 becomes the text '750' (3 characters). SUBSTRING('Virat Kohli', 7, 5) starts at K (position 7) and takes 5 characters → 'Kohli'.

Example — length, initial and last 3 letters

SELECT
    name,
    LEN(name)      AS name_length,
    LEFT(name, 1)  AS initial,
    RIGHT(name, 3) AS last3
FROM players
ORDER BY LEN(name) DESC, name;

Functions work in ORDER BY too — here we sort by the length of the name.

Example — dynamic extraction with CHARINDEX

Fixed positions break when names have different lengths. CHARINDEX(search, text) returns the position of the first match (0 if not found), so we can find the space and cut around it:

SELECT
    name,
    CHARINDEX(' ', name)                                   AS space_at,
    LEFT(name, CHARINDEX(' ', name) - 1)                   AS first_part,
    SUBSTRING(name, CHARINDEX(' ', name) + 1, LEN(name))   AS last_part
FROM players;

How it works: the first name is everything before the space (space_at - 1 characters). The last name starts after the space (space_at + 1). Using LEN(name) as the length is a common trick meaning "take everything until the end" — SUBSTRING simply stops when the string runs out.

Common mistakes

• ❌ SUBSTRING(name, 0, 5) thinking positions start at 0. ✅ SQL positions start at 1.
• ❌ Forgetting the space counts: LEN('MS Dhoni') is 8, not 7.
• ❌ Expecting LEN('Dhoni ') to be 8. ✅ SQL Server's LEN ignores trailing spaces → 5 (leading spaces are counted). Use DATALENGTH to count bytes including trailing spaces.
• ❌ Hard-coding SUBSTRING(name, 7, 5) for every row. ✅ Combine with CHARINDEX / LEN for a dynamic position and length.

SQL Server vs MySQL / PostgreSQL

Interview tip

Q: Extract the first name from a full name stored in one column. LEFT(name, CHARINDEX(' ', name) - 1). Mention the edge case: if a name has no space, CHARINDEX returns 0 and LEFT(name, -1) raises an error in SQL Server — guard it with CASE WHEN CHARINDEX(' ', name) > 0 THEN … ELSE name END.