SQL String & Numeric Functions — CONCAT, SUBSTRING, ROUND, and More

Why built-in functions matter

SQL's built-in functions let you clean, format, and compute values inside the database — without round-tripping data to the application layer. For data pipelines, reporting, and ETL work, server-side functions are often the fastest and most concise option.

String functions — concatenation and formatting

-- CONCAT: join strings (NULL-safe in MySQL, propagates NULL in Postgres)
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

-- Postgres: use || operator (propagates NULL) or CONCAT_WS (NULL-safe)
SELECT first_name || ' ' || last_name AS full_name FROM employees;
SELECT CONCAT_WS(' ', first_name, middle_name, last_name) AS full_name
FROM employees;
-- CONCAT_WS skips NULL arguments (middle_name may be NULL)

-- Build a full address string
SELECT CONCAT_WS(', ', street, city, postcode, country) AS address
FROM shipping_addresses WHERE order_id = 1042;

UPPER, LOWER, INITCAP

-- Normalise email to lowercase for comparison (case-insensitive lookup)
SELECT * FROM users WHERE LOWER(email) = LOWER('Alice@EXAMPLE.COM');

-- Display names in title case
SELECT INITCAP(product_name) AS display_name FROM products;  -- Postgres
-- → 'usb-c hub' becomes 'Usb-C Hub'

-- Store email in lowercase to avoid case-sensitivity bugs
UPDATE users SET email = LOWER(email) WHERE email <> LOWER(email);

LENGTH and SUBSTR / SUBSTRING

-- Character count (use OCTET_LENGTH for byte count with multibyte chars)
SELECT product_name, LENGTH(product_name) AS name_length FROM products
ORDER BY name_length DESC LIMIT 10;

-- Extract part of a string (1-based position)
SELECT SUBSTRING(order_reference, 1, 3) AS region_code FROM orders;
-- 'LON-20260620-1042' → 'LON'

-- Extract after a delimiter (Postgres SPLIT_PART)
SELECT SPLIT_PART(sku, '-', 1) AS category_code FROM products;
-- 'ELEC-KB-001' → 'ELEC'

TRIM, LTRIM, RTRIM — cleaning whitespace

-- Remove leading/trailing spaces from imported CSV data
UPDATE customers
SET email = TRIM(email)
WHERE email <> TRIM(email);

-- Remove specific characters (e.g., stray commas or quotes)
SELECT TRIM(BOTH ',' FROM raw_tag) AS tag FROM import_tags;

-- Detect empty-after-trim strings
SELECT * FROM products WHERE TRIM(product_name) = '';

REPLACE and REGEXP_REPLACE

-- Replace a substring literally
SELECT REPLACE(product_description, 'Ltd.', 'Limited') AS description
FROM products;

-- Strip non-digit characters from a phone number
SELECT REGEXP_REPLACE(phone, '[^0-9]', '', 'g') AS digits_only
FROM customers;
-- '+44 (020) 1234-5678' → '442012345678'

-- Mask the middle digits of a card number
SELECT REGEXP_REPLACE(card_last4, '.', '*', 'g') FROM payment_methods;

POSITION / STRPOS — finding substrings

-- Find the position of '@' in an email to extract the domain
SELECT
    email,
    SUBSTRING(email FROM POSITION('@' IN email) + 1) AS domain
FROM users;
-- 'alice@example.com' → 'example.com'

-- Filter rows where a string contains a specific word
SELECT * FROM support_tickets WHERE POSITION('refund' IN LOWER(description)) > 0;

Numeric functions — rounding and math

-- ROUND: round to a specific number of decimal places
SELECT
    product_name,
    unit_price,
    ROUND(unit_price * 1.20, 2) AS price_with_vat
FROM products;

-- CEIL / CEILING: round up; FLOOR: round down
SELECT
    CEIL(7.1)   AS ceil_result,   -- → 8
    FLOOR(7.9)  AS floor_result,  -- → 7
    ROUND(7.5)  AS round_result   -- → 8
;

-- MOD: remainder (useful for pagination, alternating rows, batch IDs)
SELECT id, product_name
FROM products
WHERE MOD(id, 3) = 0;  -- every third product (batch processing)

-- ABS: absolute value
SELECT id, ABS(balance) AS abs_balance FROM accounts WHERE balance < 0;

-- POWER and SQRT
SELECT ROUND(SQRT(area_sqm), 2) AS side_length FROM floor_plans;
SELECT POWER(2, 10) AS two_to_the_ten;  -- → 1024

Aggregate numeric functions

-- Revenue statistics per product category
SELECT
    category,
    COUNT(*)                      AS product_count,
    ROUND(AVG(unit_price), 2)     AS avg_price,
    ROUND(STDDEV(unit_price), 2)  AS price_stddev,
    MIN(unit_price)               AS cheapest,
    MAX(unit_price)               AS most_expensive,
    SUM(unit_price * stock_qty)   AS inventory_value
FROM products
GROUP BY category
ORDER BY inventory_value DESC;

STRING_AGG — aggregating strings

-- Comma-separated list of product names per order
SELECT
    order_id,
    STRING_AGG(p.product_name, ', ' ORDER BY p.product_name) AS items
FROM order_items oi
JOIN products    p ON p.id = oi.product_id
GROUP BY order_id;
-- → order 1042: 'Keyboard, Mouse, USB Hub'

Recap

Use CONCAT_WS over CONCAT when any argument may be NULL — it skips NULLs rather than propagating them. Normalise strings with TRIM and LOWER during import. Use REGEXP_REPLACE for flexible cleaning of phone numbers, postcodes, and free-text fields. Always use NUMERIC or ROUND(..., 2) for monetary arithmetic — never leave financial values as unrounded floats in the database.

More ways to practice