SQL String & Date Functions

String Functions

-- Length
SELECT LENGTH('hello');           -- 5
SELECT CHAR_LENGTH('hello');      -- 5 (alias)

-- Case
SELECT UPPER('hello');            -- HELLO
SELECT LOWER('HELLO');            -- hello

-- Trim whitespace
SELECT TRIM('  hello  ');         -- 'hello'
SELECT LTRIM('  hello');          -- 'hello'
SELECT RTRIM('hello  ');          -- 'hello'
SELECT TRIM(BOTH 'x' FROM 'xxhelloxx');  -- 'hello'

-- Substring
SELECT SUBSTRING('hello world', 7, 5);   -- 'world' (start, length)
SELECT SUBSTR('hello', 2, 3);            -- 'ell'
SELECT LEFT('hello world', 5);           -- 'hello'
SELECT RIGHT('hello world', 5);          -- 'world'

-- Position / Find
SELECT POSITION('world' IN 'hello world');  -- 7
SELECT STRPOS('hello world', 'world');      -- 7 (PostgreSQL)

-- Replace
SELECT REPLACE('hello world', 'world', 'SQL');  -- 'hello SQL'

-- Concatenation
SELECT CONCAT('hello', ' ', 'world');     -- 'hello world'
SELECT 'hello' || ' ' || 'world';        -- 'hello world' (PostgreSQL)
SELECT CONCAT_WS(', ', 'Alice', 'Bob', 'Charlie');  -- 'Alice, Bob, Charlie'

-- Padding
SELECT LPAD('42', 5, '0');    -- '00042'
SELECT RPAD('hi', 5, '.');    -- 'hi...'

-- Repeat
SELECT REPEAT('ab', 3);       -- 'ababab'

-- Reverse
SELECT REVERSE('hello');      -- 'olleh'

---

LIKE / ILIKE Pattern Matching

-- Wildcards: % = any sequence, _ = single character
WHERE name LIKE 'A%'          -- starts with A
WHERE name LIKE '%son'        -- ends with son
WHERE name LIKE '%ann%'       -- contains ann
WHERE name LIKE '_ohn'        -- 4-char ending in ohn (John, Bohn...)
WHERE email LIKE '%@gmail.%'  -- gmail addresses

-- ILIKE = case insensitive (PostgreSQL)
WHERE name ILIKE 'alice%'

-- SIMILAR TO = SQL regex-lite
WHERE name SIMILAR TO '(Alice|Bob)%'

-- Regular expressions (PostgreSQL)
WHERE name ~ '^[A-Z]'    -- starts with uppercase (case-sensitive)
WHERE name ~* '^alice'   -- case-insensitive regex

---

Date & Time Functions

-- Current date/time
SELECT NOW();               -- current timestamp with timezone
SELECT CURRENT_TIMESTAMP;   -- same
SELECT CURRENT_DATE;        -- date only: 2026-05-03
SELECT CURRENT_TIME;        -- time only

-- Extract components
SELECT EXTRACT(YEAR  FROM NOW());   -- 2026
SELECT EXTRACT(MONTH FROM NOW());   -- 5
SELECT EXTRACT(DAY   FROM NOW());   -- 3
SELECT EXTRACT(DOW   FROM NOW());   -- 0=Sunday, 6=Saturday
SELECT EXTRACT(HOUR  FROM NOW());

-- Date truncation
SELECT DATE_TRUNC('month', NOW());  -- 2026-05-01 00:00:00
SELECT DATE_TRUNC('year',  NOW());  -- 2026-01-01 00:00:00
SELECT DATE_TRUNC('week',  NOW());  -- Monday of current week

-- Arithmetic
SELECT NOW() + INTERVAL '7 days';
SELECT NOW() - INTERVAL '1 month';
SELECT '2026-05-03'::DATE - '2026-01-01'::DATE;  -- 122 (days between)

-- AGE
SELECT AGE('2026-05-03', '1998-01-15');  -- 28 years 3 mons 18 days
SELECT EXTRACT(YEAR FROM AGE(birthdate)) AS age_years FROM users;

-- Format
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS');  -- '2026-05-03 14:30:00'
SELECT TO_CHAR(NOW(), 'Month DD, YYYY');           -- 'May       03, 2026'

-- Parse
SELECT TO_DATE('03/05/2026', 'DD/MM/YYYY');       -- 2026-05-03
SELECT TO_TIMESTAMP('2026-05-03 14:30', 'YYYY-MM-DD HH24:MI');

---

Common Date Filters

-- Last 30 days
WHERE created_at >= NOW() - INTERVAL '30 days'

-- Current month
WHERE DATE_TRUNC('month', created_at) = DATE_TRUNC('month', NOW())

-- Specific year
WHERE EXTRACT(YEAR FROM created_at) = 2026
-- Better (uses index):
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'

-- Yesterday
WHERE created_at::DATE = CURRENT_DATE - 1

-- Last 7 complete days (excluding today)
WHERE created_at >= CURRENT_DATE - 7 AND created_at < CURRENT_DATE

---

NULL-Handling Functions

-- COALESCE — return first non-NULL
SELECT COALESCE(phone, mobile, email, 'no contact') FROM users;

-- NULLIF — return NULL if two args are equal (avoid divide-by-zero)
SELECT revenue / NULLIF(orders, 0) AS avg_order_value FROM summary;

-- NULLIF for blank string handling
SELECT NULLIF(TRIM(name), '') FROM users;  -- blank string → NULL

-- IS NULL / IS NOT NULL
SELECT * FROM orders WHERE completed_at IS NULL;

-- NVL (Oracle) / IFNULL (MySQL) — 2-arg COALESCE
SELECT IFNULL(phone, 'N/A') FROM users;   -- MySQL
SELECT NVL(phone, 'N/A') FROM users;      -- Oracle

---

Type Casting

-- CAST syntax (ANSI standard)
SELECT CAST('42' AS INTEGER);
SELECT CAST('2026-05-03' AS DATE);
SELECT CAST(salary AS TEXT);

-- PostgreSQL :: shorthand
SELECT '42'::INT;
SELECT '3.14'::NUMERIC(10,2);
SELECT '2026-05-03'::DATE;
SELECT 42::TEXT;

-- Common conversions
SELECT TO_NUMBER('1,234.56', '9,999.99');  -- 1234.56
SELECT TO_CHAR(1234.56, 'FM$9,999.00');   -- '$1,234.56'

---

Conditional Functions

-- IIF (SQL Server / some DBs) — inline if
SELECT IIF(salary > 100000, 'High', 'Normal') FROM employees;

-- CASE in aggregate
SELECT
  SUM(CASE WHEN status = 'delivered' THEN amount ELSE 0 END) AS delivered_revenue,
  SUM(CASE WHEN status = 'cancelled' THEN amount ELSE 0 END) AS cancelled_revenue
FROM orders;

-- CASE as pivot
SELECT
  user_id,
  MAX(CASE WHEN metric = 'clicks'     THEN value END) AS clicks,
  MAX(CASE WHEN metric = 'impressions' THEN value END) AS impressions,
  MAX(CASE WHEN metric = 'conversions' THEN value END) AS conversions
FROM metrics
GROUP BY user_id;

---

JSON Functions (PostgreSQL)

-- Access JSON field
SELECT data->>'name' FROM users;           -- text value
SELECT data->'address'->>'city' FROM users; -- nested

-- Filter on JSON field
SELECT * FROM events WHERE data->>'type' = 'click';

-- JSONB contains
SELECT * FROM products WHERE tags @> '["sale"]';

-- Extract array element
SELECT data->'items'->0->>'sku' FROM orders;  -- first item's SKU

---

Related

• [[Basics]] — WHERE, CASE WHEN, LIKE
• [[Aggregates]] — aggregate functions, GROUP BY
• [[Window Functions]] — date-based partitions and frames