String & Math Functions

Tutorial 8 of 11

Over 30 built-in scalar functions are available for string manipulation, math, and conditional logic. This tutorial walks through the most useful ones with a product catalog dataset. Every function runs in the block-oriented executor—35% faster than PostgreSQL on the scalar_functions benchmark.

1. Schema and seed data

CREATE TABLE products (
    id       SERIAL PRIMARY KEY,
    name     TEXT NOT NULL,
    sku      TEXT NOT NULL,
    price    FLOAT NOT NULL,
    discount FLOAT,
    category TEXT,
    notes    TEXT
);

INSERT INTO products (name, sku, price, discount, category, notes) VALUES
    ('Wireless Mouse',     'WM-2024-PRO',  49.99,  0.10, 'Electronics', 'Best seller'),
    ('USB-C Hub',          'HUB-7PORT',    34.50,  NULL, 'Electronics', 'New arrival'),
    ('Standing Desk',      'DESK-ADJ-60',  599.00, 0.15, 'Furniture',   NULL),
    ('Ergonomic Chair',    'CHAIR-ERG-V2', 449.99, 0.20, 'Furniture',   'Updated model'),
    ('Mechanical Keyboard','KB-MX-BLUE',    89.95, 0.05, 'Electronics', 'Cherry MX Blue'),
    ('Monitor Arm',        'ARM-DUAL-27',  129.00, NULL, 'Accessories', NULL),
    ('Desk Lamp',          'LAMP-LED-DIM', 45.00,  0.10, 'Accessories', 'Dimmable LED');

2. String case and length

UPPER, LOWER, and LENGTH are the workhorses of string processing. Use them for normalization, display formatting, and validation.

SELECT name,
       UPPER(name) AS upper_name,
       LOWER(sku) AS lower_sku,
       LENGTH(name) AS name_len
FROM products
WHERE LENGTH(name) > 10
ORDER BY name_len DESC;

3. SUBSTRING and REPLACE

SUBSTRING(str FROM start FOR length) extracts a portion. REPLACE(str, from, to) swaps substrings.

SELECT sku,
       SUBSTRING(sku FROM 1 FOR 3)
         AS prefix,
       REPLACE(sku, '-', '/')
         AS slash_sku
FROM products
ORDER BY sku;

4. CONCAT and string concatenation

CONCAT(a, b, ...) joins strings together. Unlike the || operator, CONCAT treats NULL as an empty string.

SELECT name,
       CONCAT(category, ': ', name)
         AS full_label
FROM products
ORDER BY category, name;

5. Math functions: ABS, ROUND, POWER, MOD

Compute discounted prices with ROUND, use ABS for absolute values, and MOD for modular arithmetic.

SELECT name, price,
       COALESCE(discount, 0) AS disc,
       ROUND(price * (1 - COALESCE(
         discount, 0))::numeric, 2)
         AS sale_price,
       ABS(price - 100) AS dist_from_100,
       MOD(id, 3) AS group_num
FROM products
ORDER BY sale_price DESC;

6. COALESCE and NULLIF

COALESCE(a, b, ...) returns the first non-NULL argument. NULLIF(a, b) returns NULL if a = b, otherwise returns a. Together they handle missing data gracefully.

SELECT name,
       COALESCE(notes, 'No notes')
         AS display_notes,
       COALESCE(discount, 0) * 100
         AS disc_pct,
       NULLIF(category, 'Electronics')
         AS non_elec_cat
FROM products
ORDER BY name;

7. CASE WHEN for conditional logic

CASE WHEN is SQL’s if/else. Use it to categorize, bucket, or transform values conditionally.

SELECT name, price,
       CASE
         WHEN price >= 400 THEN 'premium'
         WHEN price >= 80  THEN 'mid-range'
         ELSE 'budget'
       END AS tier,
       CASE
         WHEN discount IS NULL THEN 'full price'
         WHEN discount >= 0.15 THEN 'big sale'
         ELSE 'small discount'
       END AS pricing
FROM products
ORDER BY price DESC;

What you learned

In this tutorial you transformed a 7-product catalog using 12 scalar functions and conditional expressions:

• UPPER / LOWER for case normalization
• LENGTH for string measurement and filtering
• SUBSTRING for extracting portions of strings
• REPLACE for find-and-replace within strings
• CONCAT for NULL-safe string joining
• ABS / ROUND / MOD for numeric computation
• COALESCE for default values on NULLs
• NULLIF for conditional NULL generation
• CASE WHEN for inline conditional logic