String and Numeric Functions

These are the utility belt. Nothing here is conceptually hard, but three specific things show up in interviews repeatedly: 1-based string indexing, integer division, and the fact that most of these functions return NULL if any input is NULL.

String functions

SELECT
    CONCAT(first_name, ' ', last_name)    AS full_name,
    UPPER(email)                          AS email_upper,
    LENGTH(first_name)                    AS name_length,
    SUBSTRING(phone, 1, 3)                AS area_code,
    TRIM(BOTH ' ' FROM address)           AS clean_address,
    REPLACE(phone, '-', '')               AS digits_only,
    LEFT(description, 50)                 AS preview
FROM employees;

Off-by-one is the trap. SQL strings are 1-indexed, unlike every programming language

you use, so SUBSTRING(s, 1, 3) is the first three characters.

NULL propagates. CONCAT(first, ' ', last) is NULL in most engines when either name is

NULL — though MySQL's CONCAT returns NULL while its CONCAT_WS skips NULL arguments. Use COALESCE on anything nullable; see NULL handling.

Splitting a full name

The classic string question:

SELECT
    SUBSTRING(full_name, 1, POSITION(' ' IN full_name) - 1)  AS first_name,
    SUBSTRING(full_name, POSITION(' ' IN full_name) + 1)     AS last_name
FROM people
WHERE POSITION(' ' IN full_name) > 0;    -- guard: no space ⇒ negative length ⇒ error

That guard is the point of the question. POSITION returns 0 when the substring is absent, which makes the length -1 and either errors or returns nonsense. Say it before you are asked. For the last word of a multi-word name, most engines have SUBSTRING_INDEX(full_name, ' ', -1) (MySQL) or SPLIT_PART(full_name, ' ', 2) (PostgreSQL), which are cleaner.

Capitalising, reversing, counting

-- Title-case a name (portable form)
SELECT UPPER(SUBSTRING(name, 1, 1)) || LOWER(SUBSTRING(name, 2)) FROM people;
-- PostgreSQL has INITCAP(name)

-- Palindrome check
SELECT word FROM words WHERE word = REVERSE(word);

-- Count occurrences of a character: length minus length-with-it-removed
SELECT LENGTH(csv) - LENGTH(REPLACE(csv, ',', '')) AS comma_count FROM rows;

That last idiom — subtracting the length after REPLACE — is the standard way to count occurrences without a regex, and it comes up more often than it should.

Numeric functions

SELECT
    ROUND(salary / 12.0, 2)      AS monthly,
    CEIL(price)                  AS price_up,
    FLOOR(price)                 AS price_down,
    ABS(balance)                 AS magnitude,
    MOD(employee_id, 2)          AS parity,     -- or employee_id % 2
    POWER(base, 2)               AS squared,
    GREATEST(a, b, c)            AS largest     -- row-wise, unlike MAX
FROM …;

GREATEST/LEAST compare values across columns in one row; MAX/MIN aggregate

down a column across rows. Confusing the two is a common slip.

The integer division trap

SELECT 5 / 2;             -- 2 in PostgreSQL, SQL Server, Oracle. 2.5 in MySQL/SQLite.

-- Percentage of employees in Sales — returns 0 on most engines
SELECT COUNT(*) / (SELECT COUNT(*) FROM employees) FROM employees WHERE dept = 'Sales';

-- Fix: force floating point
SELECT 100.0 * COUNT(*) / (SELECT COUNT(*) FROM employees) …
SELECT 100 * COUNT(*)::numeric / (SELECT COUNT(*) FROM employees) …   -- PostgreSQL cast

Two integers divide to an integer in most engines, so a ratio that should be 0.42 comes back as 0. Multiplying by 100.0 — note the decimal point — promotes the whole expression. This is the single most common numeric bug in interview answers.

Pair it with NULLIF on the denominator to guard division by zero: 100.0 * wins / NULLIF(games, 0).

Rounding behaviour

ROUND(2.5) is 3 in most engines (half away from zero) but banker's rounding — to the nearest even — appears in some configurations and in application layers. ROUND(x, -2) rounds to the nearest hundred, which is occasionally useful for bucketing. TRUNC/TRUNCATE cuts rather than rounds, so TRUNC(-2.7) is -2 while FLOOR(-2.7) is -3 — the distinction that matters with negative numbers.

Mistakes that cost the interview

• 0-indexing strings. SQL starts at 1.
• Integer division, returning 0 for a percentage.
• No guard when `POSITION` returns 0 in a split.
• Forgetting NULL propagation through CONCAT and arithmetic.
• `GREATEST` vs `MAX` confusion.
• `LENGTH` on multi-byte text, counting bytes rather than characters.

Practise these on the SQL sheet.