Using SQL Functions
1. Using String Functions
| Function | Result |
|---|---|
| LENGTH(s) | Char count |
| UPPER(s) / LOWER(s) | Case conversion |
| TRIM(s) / LTRIM / RTRIM | Strip whitespace |
| SUBSTRING(s FROM x FOR n) | Slice |
| REPLACE(s, a, b) | Substitute |
| CONCAT(a,b) / a || b | Concatenate |
| POSITION(sub IN s) | Find index |
| SPLIT_PART(s, d, n) | Nth split (PG) |
| REGEXP_REPLACE / REGEXP_MATCH | Regex ops |
| FORMAT(fmt, args) | printf-like (PG) |
2. Using Numeric Functions
| Function | Returns |
|---|---|
| ABS, SIGN | Magnitude / sign |
| CEIL, FLOOR, ROUND(x, n) | Rounding |
| MOD(a,b) / % | Remainder |
| POWER(x, y), SQRT(x) | Exponent / root |
| LN, LOG, EXP | Logs / exponential |
| SIN/COS/TAN/... | Trig |
| RANDOM() | 0–1 random (PG); MySQL: RAND() |
| GREATEST / LEAST | Max/min across args |
3. Using Date Functions
| Function | Use |
|---|---|
| NOW() / CURRENT_TIMESTAMP | Now (with tz) |
| CURRENT_DATE / CURRENT_TIME | Date / time only |
| date_trunc('month', ts) | Truncate to unit (PG) |
| EXTRACT(YEAR FROM ts) | Get field |
| AGE(t1, t2) / t1 - t2 | Interval |
| +/- INTERVAL '1 day' | Arithmetic |
| to_char(ts, 'YYYY-MM-DD') | Format |
| to_timestamp(text, fmt) | Parse |
4. Using Conversion Functions
| Function | Use |
|---|---|
| CAST(x AS type) | Standard conversion |
| x::type | Postgres shorthand |
| CONVERT(type, x) | SQL Server / MySQL |
| to_number / to_char / to_date | Format-specific (PG/Oracle) |
| TRY_CAST / SAFE_CAST | Return NULL on failure (some DBs) |
5. Using Conditional Functions
| Function | Example |
|---|---|
| CASE | CASE WHEN x > 0 THEN 'pos' WHEN x < 0 THEN 'neg' ELSE 'zero' END |
| COALESCE | First non-null |
| NULLIF | NULL if a=b |
| IIF (SQL Server) | IIF(cond, then, else) |
| IF (MySQL) | IF(cond, then, else) |
| DECODE (Oracle) | Legacy switch |
6. Using Window Functions
| Function | Purpose |
|---|---|
| ROW_NUMBER() | Sequential rank per partition |
| RANK() / DENSE_RANK() | With ties (gap / no gap) |
| NTILE(n) | Buckets 1..n |
| LAG(col, n) / LEAD(col, n) | Previous / next row value |
| FIRST_VALUE / LAST_VALUE | Window endpoints |
| PERCENT_RANK / CUME_DIST | Percentile |
Example: Greatest-N-per-group
SELECT *
FROM (
SELECT o.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY placed_at DESC) AS rn
FROM orders o
) x
WHERE rn <= 3; -- 3 most recent per customer7. Using Aggregate Window Functions
| Pattern | Example |
|---|---|
| Running total | SUM(amt) OVER (ORDER BY ts ROWS UNBOUNDED PRECEDING) |
| Moving average | AVG(x) OVER (ORDER BY ts ROWS 6 PRECEDING) |
| Per-group total | SUM(x) OVER (PARTITION BY region) |
| Frame | ROWS / RANGE / GROUPS modes |
8. Creating User-Defined Functions
| Aspect | Detail |
|---|---|
| Languages | SQL, PL/pgSQL, PL/Python, PL/V8, Java (Oracle) |
| Volatility (PG) | IMMUTABLE, STABLE, VOLATILE — affects caching |
| SECURITY DEFINER | Run as creator (use cautiously) |
| Inlining | Simple SQL fns may be inlined into plans |
Example: PL/pgSQL Function
CREATE FUNCTION discount(p NUMERIC, pct NUMERIC)
RETURNS NUMERIC LANGUAGE plpgsql IMMUTABLE AS $
BEGIN
RETURN p * (1 - pct/100.0);
END;
$;9. Creating Scalar Functions
| Property | Detail |
|---|---|
| Returns | Single value per call |
| Use in | SELECT, WHERE, ORDER BY, CHECK |
| Caveat | Can prevent index use if applied to indexed column |
| Mitigation | Use generated columns or function-based indexes |
10. Creating Table-Valued Functions
| Type | Detail |
|---|---|
| Inline TVF | Single SELECT, planner can inline |
| Multi-statement TVF | Returns built-up rows, opaque to planner |
| SETOF / TABLE return | PG syntax for table-returning functions |
| Use | SELECT * FROM my_fn(args) |