SQL String and Date Functions: What Exams Test

String functions

Common string functions for manipulating text in SQL.

SELECT
    UPPER(name)              AS upper_name,
    LOWER(email)             AS lower_email,
    LENGTH(name)             AS name_length,
    TRIM('  hello  ')        AS trimmed,       -- 'hello'
    LTRIM('  hello  ')       AS left_trimmed,  -- 'hello  '
    RTRIM('  hello  ')       AS right_trimmed, -- '  hello'
    SUBSTRING(name, 1, 3)    AS first_3_chars,
    REPLACE(email, '@old.com', '@new.com') AS new_email,
    CONCAT(first_name, ' ', last_name) AS full_name,
    POSITION('@' IN email)   AS at_position
FROM employees;

Date functions

Date manipulation varies slightly between databases, but these patterns are universal.

-- Current date/time
SELECT NOW();           -- current timestamp
SELECT CURRENT_DATE;    -- current date only
SELECT CURRENT_TIME;    -- current time only

-- Extract parts
SELECT
    EXTRACT(YEAR FROM hire_date)  AS hire_year,
    EXTRACT(MONTH FROM hire_date) AS hire_month,
    EXTRACT(DOW FROM hire_date)   AS day_of_week;

-- Date arithmetic
SELECT
    hire_date + INTERVAL '30 days' AS plus_30_days,
    NOW() - hire_date              AS tenure;

-- Date truncation (PostgreSQL)
SELECT DATE_TRUNC('month', hire_date) AS hire_month
FROM employees;

CASE expressions

CASE lets you add conditional logic inside a SELECT.

-- Simple CASE
SELECT name,
    CASE salary
        WHEN 100000 THEN 'Senior'
        WHEN 80000  THEN 'Mid'
        ELSE 'Junior'
    END AS level
FROM employees;

-- Searched CASE (more flexible)
SELECT name, salary,
    CASE
        WHEN salary >= 100000 THEN 'Senior'
        WHEN salary >= 70000  THEN 'Mid'
        ELSE 'Junior'
    END AS level
FROM employees;

-- CASE in aggregates
SELECT
    COUNT(CASE WHEN salary >= 100000 THEN 1 END) AS senior_count,
    COUNT(CASE WHEN salary <  100000 THEN 1 END) AS other_count
FROM employees;

COALESCE and NULLIF

COALESCE returns the first non-NULL value. NULLIF returns NULL if two values are equal.

-- COALESCE: first non-null value
SELECT
    COALESCE(phone, mobile, 'No contact') AS contact
FROM employees;

-- NULLIF: returns NULL if values are equal (avoids divide by zero)
SELECT
    total_sales / NULLIF(total_orders, 0) AS avg_order_value
FROM sales;
-- If total_orders = 0, NULLIF returns NULL, avoiding division by zero

Exam tip

COALESCE and NULLIF are frequently tested. COALESCE(a, b, c) returns the first non-NULL. NULLIF(a, b) returns NULL when a = b — its primary use is preventing division-by-zero errors.