SQL Fonctions Référence

String Functions

SQL string functions manipulate character data and return string or numeric results. Common functions include LENGTH for character count, SUBSTRING for extraction, and UPPER or LOWER for case conversion. Concatenation uses the standard operator or the CONCAT function, with dialect-specific variations. TRIM removes leading, trailing, or both characters, defaulting to spaces. String functions often have encoding-aware behavior that counts characters rather than bytes.

LENGTH(s)

Number of characters in string

LENGTH('abc') = 3

SUBSTRING(s, pos, len)

Extract substring from position

SUBSTRING('abc',2,1) = b

UPPER(s) / LOWER(s)

Convert case

UPPER('a') = A

TRIM(s)

Remove leading and trailing spaces

TRIM(' a ') = a

REPLACE(s, from, to)

Replace all occurrences

REPLACE('a-b','-','_') = a_b

Aggregate Functions

Aggregate functions reduce a group of rows to a single value. The standard aggregates are COUNT, SUM, AVG, MIN, and MAX, all of which ignore null inputs except COUNT(*). The GROUP BY clause divides rows into groups before aggregation, and HAVING filters groups after aggregation. Distinct inside an aggregate eliminates duplicate values before computing. Window functions apply aggregates over a sliding frame without collapsing rows.

Window Functions

Window functions compute a result per row based on a window of related rows defined by PARTITION BY and ORDER BY. The frame clause specifies which rows in the partition contribute, such as ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Ranking functions like ROW_NUMBER, RANK, and DENSE_RANK assign positions within the partition. Window functions are evaluated after WHERE and GROUP BY but before ORDER BY in the logical query processing order.