# Fonctions utiles dans la réalisation des requêtes
Vous trouverez ici des fonctions qui permettent de répondre à certaines demandes particulières. Volontairement, ***nous n'avons pas tout mis***.
Nous les avons triées en 4 catégories :
- [[#Les Fonctions Numériques]]
- [[#Les Fonctions Dates]]
- [[#Les Fonctions Textes]]
- [[#Les Fonctions Conditionnelles]]
## Les Fonctions Numériques
### Opérateurs Arithmétiques de Base
| Opérateur | Description | Exemple SQL | Résultat |
| :----------- | :------------------------------------ | :--------------------------------------- | :------- |
| `+` | Addition | `SELECT 10 + 5;` | `15` |
| `-` | Soustraction | `SELECT 10 - 5;` | `5` |
| `*` | Multiplication | `SELECT 10 * 5;` | `50` |
| `/` | Division | `SELECT 10 / 4;` | `2.5` |
| `%` ou `MOD` | Modulo (reste de la division entière) | `SELECT 10 % 3;` ou `SELECT MOD(10, 3);` | `1` |
> [!note] Division par zéro
> En MySQL, la division par zéro (`/`) ne produit pas d'erreur mais retourne `NULL`.
> `SELECT 10 / 0;` retourne `NULL`.
### Valeur Absolue
* **`ABS(X)`**
* **Description** : Retourne la valeur absolue de `X`.
* **Syntaxe** : `ABS(nombre)`
* **Exemple** :
```sql
SELECT ABS(-15), ABS(15), ABS(0);
```
| ABS(-15) | ABS(15) | ABS(0) |
| :------- | :------ | :----- |
| 15 | 15 | 0 |
### Puissance et Racine Carrée
* **`POW(X, Y)`** ou **`POWER(X, Y)`**
* **Description** : Retourne `X` élevé à la puissance `Y` ($X^Y$).
* **Syntaxe** : `POW(base, exposant)`
* **Exemple** :
```sql
SELECT POW(2, 3), POWER(5, 2);
```
| POW(2, 3) | POWER(5, 2) |
| :-------- | :---------- |
| 8 | 25 |
* **`SQRT(X)`**
* **Description** : Retourne la racine carrée non négative de `X` ($\sqrt{X}$). `X` doit être positif.
* **Syntaxe** : `SQRT(nombre_positif)`
* **Exemple** :
```sql
SELECT SQRT(9), SQRT(2);
```
| SQRT(9) | SQRT(2) |
| :------ | :----------------- |
| 3 | 1.4142135623730951 |
> [!warning] Racine carrée d'un nombre négatif
> `SQRT()` retourne `NULL` si l'argument est négatif.
> `SELECT SQRT(-4);` retourne `NULL`.
### Exponentielle et Logarithme
* **`EXP(X)`**
* **Description** : Retourne la valeur de $e$ (base du logarithme naturel) élevée à la puissance `X` ($e^X$).
* **Syntaxe** : `EXP(exposant)`
* **Exemple** :
```sql
SELECT EXP(1), EXP(0); -- e^1 et e^0
```
| EXP(1) | EXP(0) |
| :----------------- | :----- |
| 2.718281828459045 | 1 |
* **`LOG(X)`** ou **`LN(X)`**
* **Description** : Retourne le logarithme naturel (base $e$) de `X`.
* **Syntaxe** : `LOG(nombre_positif)` ou `LN(nombre_positif)`
* **Exemple** :
```sql
SELECT LOG(2.71828), LN(10);
```
| LOG(2.71828) | LN(10) |
| :----------------- | :----------------- |
| 0.9999996160126781 | 2.302585092994046 |
* **`LOG(B, X)`** (à partir de MySQL 8.0)
* **Description** : Retourne le logarithme de `X` en base `B`.
* **Syntaxe** : `LOG(base, nombre_positif)`
* **Exemple** :
```sql
SELECT LOG(10, 100); -- log base 10 de 100
```
| LOG(10, 100) |
| :----------- |
| 2 |
* **`LOG10(X)`**
* **Description** : Retourne le logarithme en base 10 de `X`.
* **Syntaxe** : `LOG10(nombre_positif)`
* **Exemple** :
```sql
SELECT LOG10(1000);
```
| LOG10(1000) |
| :---------- |
| 3 |
> [!warning] Arguments des fonctions logarithmiques
> Les arguments des fonctions logarithmiques (`LOG`, `LN`, `LOG10`) doivent être strictement positifs. Un argument $\le 0$ retournera `NULL`.
### Arrondi à l'entier le plus proche
* **`ROUND(X)`**
* **Description** : Arrondit `X` à l'entier le plus proche. Les valeurs se terminant par `.5` sont arrondies à l'entier supérieur (règle des "arrondis au plus proche, moitié à l'extérieur" ou "round half away from zero").
* **Syntaxe** : `ROUND(nombre)`
* **Exemple** :
```sql
SELECT ROUND(3.14), ROUND(3.7), ROUND(3.5), ROUND(-3.5);
```
| ROUND(3.14) | ROUND(3.7) | ROUND(3.5) | ROUND(-3.5) |
| :---------- | :--------- | :--------- | :---------- |
| 3 | 4 | 4 | -4 |
* **`ROUND(X, D)`**
* **Description** : Arrondit `X` à `D` décimales. Si `D` est positif, `X` est arrondi à `D` chiffres après la virgule. Si `D` est négatif, `X` est arrondi à `D` chiffres avant la virgule (à la dizaine, centaine, etc.).
* **Syntaxe** : `ROUND(nombre, nombre_de_decimales)`
* **Exemple** :
```sql
SELECT ROUND(123.456, 2), ROUND(123.456, 0), ROUND(123.456, -1);
```
| ROUND(123.456, 2) | ROUND(123.456, 0) | ROUND(123.456, -1) |
| :---------------- | :---------------- | :----------------- |
| 123.46 | 123 | 120 |
### Arrondi à l'entier supérieur ou inférieur
* **`CEIL(X)`** ou **`CEILING(X)`**
* **Description** : Retourne le plus petit entier supérieur ou égal à `X`. (Fonction "plafond")
* **Syntaxe** : `CEIL(nombre)`
* **Exemple** :
```sql
SELECT CEIL(3.14), CEIL(3.7), CEIL(3.0), CEIL(-3.14);
```
| CEIL(3.14) | CEIL(3.7) | CEIL(3.0) | CEIL(-3.14) |
| :--------- | :-------- | :-------- | :---------- |
| 4 | 4 | 3 | -3 |
* **`FLOOR(X)`**
* **Description** : Retourne le plus grand entier inférieur ou égal à `X`. (Fonction "plancher")
* **Syntaxe** : `FLOOR(nombre)`
* **Exemple** :
```sql
SELECT FLOOR(3.14), FLOOR(3.7), FLOOR(3.0), FLOOR(-3.14);
```
| FLOOR(3.14) | FLOOR(3.7) | FLOOR(3.0) | FLOOR(-3.14) |
| :---------- | :--------- | :--------- | :----------- |
| 3 | 3 | 3 | -4 |
### Troncature d'un nombre
* **`TRUNCATE(X, D)`**
* **Description** : Tronque `X` à `D` décimales. Contrairement à `ROUND()`, `TRUNCATE()` ne fait pas d'arrondi ; elle supprime simplement les chiffres après la $D$-ième décimale.
* **Syntaxe** : `TRUNCATE(nombre, nombre_de_decimales)`
* **Exemple** :
```sql
SELECT TRUNCATE(123.456, 2), TRUNCATE(123.456, 0), TRUNCATE(123.456, -1);
```
| TRUNCATE(123.456, 2) | TRUNCATE(123.456, 0) | TRUNCATE(123.456, -1) |
| :------------------- | :------------------- | :-------------------- |
| 123.45 | 123 | 120 |
> [!note] Différence entre `ROUND()` et `TRUNCATE()`
> `ROUND(123.9, 0)` donne `124`.
> `TRUNCATE(123.9, 0)` donne `123`.
> `ROUND(123.456, -1)` donne `120`.
> `TRUNCATE(123.456, -1)` donne `120`. (Le comportement est similaire pour les décimales négatives)
## Les Fonctions Dates
### Fonctions de Récupération de la Date et l'Heure Actuelles
Ces fonctions permettent d'obtenir la date et/ou l'heure courantes du système, avec ou sans gestion du fuseau horaire.
* **`NOW([fsp])`**
* **Description** : Retourne la date et l'heure actuelles du système sous forme de valeur `DATETIME`. La valeur est basée sur l'heure de début de l'exécution de l'instruction SQL.
* **Syntaxe** : `NOW([fsp])`
* **Exemple** :
```sql
SELECT NOW(), NOW(3);
```
| NOW() | NOW(3) |
| :-------------------- | :------------------------ |
| 2023-10-27 14:30:45 | 2023-10-27 14:30:45.123 |
* **`CURDATE()`**
* **Description** : Retourne la date actuelle du système sous forme de valeur `DATE` (`'YYYY-MM-DD'`).
* **Syntaxe** : `CURDATE()`
* **Exemple** :
```sql
SELECT CURDATE();
```
| CURDATE() |
| :--------- |
| 2023-10-27 |
* **`CURTIME([fsp])`**
* **Description** : Retourne l'heure actuelle du système sous forme de valeur `TIME` (`'HH:MM:SS'`).
* **Syntaxe** : `CURTIME([fsp])`
* **Exemple** :
```sql
SELECT CURTIME(), CURTIME(2);
```
| CURTIME() | CURTIME(2) |
| :-------- | :--------- |
| 14:30:45 | 14:30:45.67|
### Fonctions d'Extraction de Composants Temporels
Ces fonctions permettent d'extraire des parties spécifiques (année, mois, jour, heure, etc.) d'une valeur de date ou d'heure.
* **`YEAR(date)`**
* **Description** : Retourne l'année pour la date donnée, sous forme d'un nombre entier à 4 chiffres.
* **Syntaxe** : `YEAR(expr)`
* **Exemple** :
```sql
SELECT YEAR('2023-10-27');
```
| YEAR('2023-10-27') |
| :----------------- |
| 2023 |
* **`MONTH(date)`**
* **Description** : Retourne le mois pour la date donnée, sous forme d'un nombre entier (1 pour janvier, 12 pour décembre).
* **Syntaxe** : `MONTH(expr)`
* **Exemple** :
```sql
SELECT MONTH('2023-10-27');
```
| MONTH('2023-10-27') |
| :------------------ |
| 10 |
* **`DAY(date)`** ou **`DAYOFMONTH(date)`**
* **Description** : Retourne le jour du mois pour la date donnée, sous forme d'un nombre entier (1 à 31). `DAY()` est un alias de `DAYOFMONTH()`.
* **Syntaxe** : `DAY(expr)` ou `DAYOFMONTH(expr)`
* **Exemple** :
```sql
SELECT DAY('2023-10-27'), DAYOFMONTH('2023-10-27');
```
| DAY('2023-10-27') | DAYOFMONTH('2023-10-27') |
| :---------------- | :----------------------- |
| 27 | 27 |
* **`WEEK(date[, mode])`**
* **Description** : Retourne le numéro de la semaine pour la date donnée. Le paramètre optionnel `mode` affecte la détermination de la première semaine de l'année et du premier jour de la semaine.
* **Syntaxe** : `WEEK(expr[, mode])`
* **Exemple** :
```sql
SELECT WEEK('2023-01-01', 0), WEEK('2023-01-01', 1);
```
| WEEK('2023-01-01', 0) | WEEK('2023-01-01', 1) |
| :-------------------- | :-------------------- |
| 0 | 52 |
> [!note] Modes de `WEEK()`
> Les modes définissent si la semaine commence le dimanche ou le lundi , et si la première semaine de l'année contient le 1er janvier ou si elle doit avoir au moins 4 jours dans la nouvelle année. Par défaut, `mode` est 0.
>
> |Mode|Premier jour|Semaine 1 = première semaine contenant…|
> |---|---|---|
> |0|Dimanche|un dimanche|
> |1|Lundi|≥ 4 jours dans l’année (ISO-like)|
> |2|Dimanche|≥ 4 jours dans l’année|
> |3|Lundi|un lundi|
> |4|Dimanche|≥ 4 jours dans l’année|
> |5|Lundi|un lundi|
> |6|Dimanche|≥ 4 jours dans l’année|
> |7|Lundi|≥ 4 jours dans l’année (ISO 8601)|
* **`WEEKOFYEAR(date)`**
* **Description** : Retourne le numéro de la semaine pour la date donnée (1-53). La semaine commence le lundi. Le 1er janvier est dans la première semaine de l'année.
* **Syntaxe** : `WEEKOFYEAR(expr)`
* **Exemple** :
```sql
SELECT WEEKOFYEAR('2023-01-01'), WEEKOFYEAR('2023-01-02');
```
| WEEKOFYEAR('2023-01-01') | WEEKOFYEAR('2023-01-02') |
| :----------------------- | :----------------------- |
| 1 | 1 |
* **`DAYOFWEEK(date)`**
* **Description** : Retourne l'index du jour de la semaine pour la date donnée (1 = dimanche, 2 = lundi, ..., 7 = samedi).
* **Syntaxe** : `DAYOFWEEK(expr)`
* **Exemple** :
```sql
SELECT DAYOFWEEK('2023-10-27'); -- Vendredi
```
| DAYOFWEEK('2023-10-27') |
| :---------------------- |
| 6 |
* **`WEEKDAY(date)`**
* **Description** : Retourne l'index du jour de la semaine pour la date donnée (0 = lundi, 1 = mardi, ..., 6 = dimanche).
* **Syntaxe** : `WEEKDAY(expr)`
* **Exemple** :
```sql
SELECT WEEKDAY('2023-10-27'); -- Vendredi
```
| WEEKDAY('2023-10-27') |
| :-------------------- |
| 4 |
* **`DAYOFYEAR(date)`**
* **Description** : Retourne le jour de l'année pour la date donnée (1 à 366).
* **Syntaxe** : `DAYOFYEAR(expr)`
* **Exemple** :
```sql
SELECT DAYOFYEAR('2023-01-01'), DAYOFYEAR('2023-12-31');
```
| DAYOFYEAR('2023-01-01') | DAYOFYEAR('2023-12-31') |
| :---------------------- | :---------------------- |
| 1 | 365 |
* **`HOUR(time)`**
* **Description** : Retourne l'heure pour la valeur de temps donnée, sous forme d'un nombre entier (0 à 23).
* **Syntaxe** : `HOUR(expr)`
* **Exemple** :
```sql
SELECT HOUR('14:30:45'), HOUR('2023-10-27 08:15:00');
```
| HOUR('14:30:45') | HOUR('2023-10-27 08:15:00') |
| :--------------- | :-------------------------- |
| 14 | 8 |
* **`MINUTE(time)`**
* **Description** : Retourne la minute pour la valeur de temps donnée, sous forme d'un nombre entier (0 à 59).
* **Syntaxe** : `MINUTE(expr)`
* **Exemple** :
```sql
SELECT MINUTE('14:30:45');
```
| MINUTE('14:30:45') |
| :----------------- |
| 30 |
* **`SECOND(time)`**
* **Description** : Retourne la seconde pour la valeur de temps donnée, sous forme d'un nombre entier (0 à 59).
* **Syntaxe** : `SECOND(expr)`
* **Exemple** :
```sql
SELECT SECOND('14:30:45');
```
| SECOND('14:30:45') |
| :----------------- |
| 45 |
* **`MICROSECOND(time)`**
* **Description** : Retourne la microseconde pour la valeur de temps donnée, sous forme d'un nombre entier (0 à 999999).
* **Syntaxe** : `MICROSECOND(expr)`
* **Exemple** :
```sql
SELECT MICROSECOND('14:30:45.123456');
```
| MICROSECOND('14:30:45.123456') |
| :----------------------------- |
| 123456 |
* **`QUARTER(date)`**
* **Description** : Retourne le trimestre de l'année pour la date donnée (1 à 4).
* **Syntaxe** : `QUARTER(expr)`
* **Exemple** :
```sql
SELECT QUARTER('2023-01-15'), QUARTER('2023-04-01');
```
| QUARTER('2023-01-15') | QUARTER('2023-04-01') |
| :-------------------- | :-------------------- |
| 1 | 2 |
* **`DATE(expr)`**
* **Description** : Extrait la partie date d'une expression de date/heure ou datetime.
* **Syntaxe** : `DATE(expr)`
* **Exemple** :
```sql
SELECT DATE('2023-10-27 14:30:45');
```
| DATE('2023-10-27 14:30:45') |
| :-------------------------- |
| 2023-10-27 |
* **`TIME(expr)`**
* **Description** : Extrait la partie heure d'une expression de date/heure ou datetime.
* **Syntaxe** : `TIME(expr)`
* **Exemple** :
```sql
SELECT TIME('2023-10-27 14:30:45');
```
| TIME('2023-10-27 14:30:45') |
| :-------------------------- |
| 14:30:45 |
### Fonctions de Manipulation et de Calcul Temporel
Ces fonctions sont utilisées pour ajouter ou soustraire des intervalles de temps, ou pour calculer des différences entre dates et heures.
* **`DATE_ADD(date, INTERVAL expr unit)`** ou **`ADDDATE(date, INTERVAL expr unit)`**
* **Description** : Ajoute un intervalle de temps spécifié à une date. `ADDDATE()` est un synonyme de `DATE_ADD()`.
* **Syntaxe** : `DATE_ADD(expr, INTERVAL value unit)` ou `ADDDATE(expr, INTERVAL value unit)`
* **`unit` possibles** : `MICROSECOND`, `SECOND`, `MINUTE`, `HOUR`, `DAY`, `WEEK`, `MONTH`, `QUARTER`, `YEAR`, `SECOND_MICROSECOND`, `MINUTE_MICROSECOND`, `MINUTE_SECOND`, `HOUR_MICROSECOND`, `HOUR_SECOND`, `HOUR_MINUTE`, `DAY_MICROSECOND`, `DAY_SECOND`, `DAY_MINUTE`, `DAY_HOUR`, `YEAR_MONTH`.
* **Exemple** :
```sql
SELECT
DATE_ADD('2023-10-27', INTERVAL 5 DAY) AS FiveDaysLater,
ADDDATE('2023-10-27 10:00:00', INTERVAL '2 03:30' DAY_MINUTE) AS ComplexAdd;
```
| FiveDaysLater | ComplexAdd |
| :------------ | :------------------ |
| 2023-11-01 | 2023-10-29 13:30:00 |
> [!note] L'opérateur `INTERVAL`
> L'opérateur `INTERVAL` est crucial pour la manipulation de dates. Il permet de spécifier des unités de temps variées, allant de la microseconde à l'année, et même des combinaisons (`DAY_HOUR`). La valeur (`expr`) doit être une chaîne de caractères si l'unité est composée (ex: `'2 03:30'` pour `DAY_MINUTE`).
* **`DATE_SUB(date, INTERVAL expr unit)`** ou **`SUBDATE(date, INTERVAL expr unit)`**
* **Description** : Soustrait un intervalle de temps spécifié à une date. `SUBDATE()` est un synonyme de `DATE_SUB()`.
* **Syntaxe** : `DATE_SUB(expr, INTERVAL value unit)` ou `SUBDATE(expr, INTERVAL value unit)`
* **`unit` possibles** : Identiques à `DATE_ADD()`.
* **Exemple** :
```sql
SELECT
DATE_SUB('2023-10-27', INTERVAL 1 MONTH) AS OneMonthAgo,
SUBDATE(CURDATE(), INTERVAL '1 12' DAY_HOUR) AS YesterdayHalfDayAgo;
```
| OneMonthAgo | YesterdayHalfDayAgo |
| :---------- | :------------------ |
| 2023-09-27 | 2023-10-26 12:00:00 |
* **`DATEDIFF(expr1, expr2)`**
* **Description** : Retourne le nombre de jours entre deux dates. Le calcul est `expr1 - expr2`.
* **Syntaxe** : `DATEDIFF(expr1, expr2)`
* **Formule** : $ \text{DATEDIFF}(D_2, D_1) = D_2 - D_1 $
où $D_1$ et $D_2$ sont des dates, et le résultat est le nombre de jours entiers entre elles.
* **Exemple** :
```sql
SELECT DATEDIFF('2023-12-31', '2023-01-01'), DATEDIFF('2023-01-01', '2023-12-31');
```
| DATEDIFF('2023-12-31', '2023-01-01') | DATEDIFF('2023-01-01', '2023-12-31') |
| :----------------------------------- | :----------------------------------- |
| 364 | -364 |
> [!note] `DATEDIFF` et l'heure
> `DATEDIFF` ne prend en compte que la partie date des expressions. L'heure est ignorée. Ainsi, la différence entre '2023-10-27 08:00:00' et '2023-10-26 23:00:00' est 1 jour.
* **`TIMEDIFF(expr1, expr2)`**
* **Description** : Retourne la différence entre deux expressions de temps ou de date/heure sous forme de valeur `TIME`. Le résultat peut être négatif.
* **Syntaxe** : `TIMEDIFF(expr1, expr2)`
* **Exemple** :
```sql
SELECT TIMEDIFF('10:00:00', '08:30:00'), TIMEDIFF('08:30:00', '10:00:00');
```
| TIMEDIFF('10:00:00', '08:30:00') | TIMEDIFF('08:30:00', '10:00:00') |
| :------------------------------- | :------------------------------- |
| 01:30:00 | -01:30:00 |
> [!warning] Plage de `TIMEDIFF`
> Le résultat de `TIMEDIFF` peut dépasser 24 heures (jusqu'à `838:59:59`), contrairement aux valeurs `TIME` normales qui sont souvent limitées à 23:59:59.
### Fonctions de Construction de Dates et Heures
Ces fonctions permettent de créer des valeurs de date ou d'heure à partir de leurs composants ou de déterminer des dates spécifiques comme le dernier jour du mois.
* **`LAST_DAY(date)`**
* **Description** : Retourne le dernier jour du mois pour la date donnée.
* **Syntaxe** : `LAST_DAY(expr)`
* **Exemple** :
```sql
SELECT LAST_DAY('2023-02-15'), LAST_DAY('2024-02-15'); -- 2024 est bissextile
```
| LAST_DAY('2023-02-15') | LAST_DAY('2024-02-15') |
| :--------------------- | :--------------------- |
| 2023-02-28 | 2024-02-29 |
* **`MAKEDATE(year, dayofyear)`**
* **Description** : Crée une date à partir de l'année (`year`) et du jour de l'année (`dayofyear`). `dayofyear` doit être supérieur à 0.
* **Syntaxe** : `MAKEDATE(year, dayofyear)`
* **Exemple** :
```sql
SELECT MAKEDATE(2023, 1), MAKEDATE(2023, 365);
```
| MAKEDATE(2023, 1) | MAKEDATE(2023, 365) |
| :---------------- | :------------------ |
| 2023-01-01 | 2023-12-31 |
* **`MAKETIME(hour, minute, second)`**
* **Description** : Crée une valeur `TIME` à partir des composants heure, minute et seconde.
* **Syntaxe** : `MAKETIME(hour, minute, second)`
* **Exemple** :
```sql
SELECT MAKETIME(14, 30, 00), MAKETIME(25, 10, 5); -- L'heure peut dépasser 23
```
| MAKETIME(14, 30, 00) | MAKETIME(25, 10, 5) |
| :------------------- | :------------------ |
| 14:30:00 | 25:10:05 |
## Les Fonctions Textes
### Fonctions de Longueur
Ces fonctions permettent de déterminer la taille d'une chaîne de caractères, mais avec des nuances importantes concernant l'unité de mesure (octets vs. caractères).
* **`LENGTH(str)`**
* **Description** : Retourne la longueur de la chaîne `str` en **octets**. C'est le nombre d'octets physiques utilisés pour stocker la chaîne.
* **Syntaxe** : `LENGTH(chaine)`
* **Exemple** :
```sql
SELECT LENGTH('Hello'), LENGTH('Héllö'), LENGTH('😀');
```
| LENGTH('Hello') | LENGTH('Héllö') | LENGTH('😀') |
| :-------------- | :-------------- | :----------- |
| 5 | 7 | 4 |
> [!note] Explication de l'exemple
> Pour `'Hello'`, chaque caractère est encodé sur 1 octet, donc 5 octets.
> Pour `'Héllö'`, en `utf8mb4` (le plus courant), 'H', 'l', 'l' sont sur 1 octet, tandis que 'é' et 'ö' sont sur 2 octets. Total: $1+2+1+1+2 = 7$ octets.
> Pour `'😀'` (un emoji), en `utf8mb4`, il est encodé sur 4 octets.
* **`CHAR_LENGTH(str)`**
* **Description** : Retourne la longueur de la chaîne `str` en **caractères**. C'est le nombre de caractères logiques, indépendamment de leur encodage en octets.
* **Syntaxe** : `CHAR_LENGTH(chaine)`
* **Exemple** :
```sql
SELECT CHAR_LENGTH('Hello'), CHAR_LENGTH('Héllö'), CHAR_LENGTH('😀');
```
| CHAR_LENGTH('Hello') | CHAR_LENGTH('Héllö') | CHAR_LENGTH('😀') |
| :------------------- | :------------------- | :---------------- |
| 5 | 5 | 1 |
> [!warning] Distinction Cruciale
> `LENGTH()` et `CHAR_LENGTH()` renvoient des résultats différents pour les caractères multi-octets (comme ceux souvent trouvés en `utf8mb4`). `CHAR_LENGTH()` est généralement ce que l'on attend quand on parle de "longueur" d'une chaîne dans un contexte humain.
### Fonctions de Conversion de Casse
Ces fonctions sont utilisées pour changer la casse (majuscules/minuscules) des caractères d'une chaîne.
* **`UPPER(str)` et `UCASE(str)`**
* **Description** : Convertit tous les caractères alphabétiques de la chaîne `str` en majuscules. `UCASE()` est un synonyme de `UPPER()`.
* **Syntaxe** : `UPPER(chaine)` ou `UCASE(chaine)`
* **Exemple** :
```sql
SELECT UPPER('Bonjour le monde'), UCASE('SQL est FANTASTIQUE');
```
| UPPER('Bonjour le monde') | UCASE('SQL est FANTASTIQUE') |
| :------------------------ | :--------------------------- |
| BONJOUR LE MONDE | SQL EST FANTASTIQUE |
* **`LOWER(str)` et `LCASE(str)`**
* **Description** : Convertit tous les caractères alphabétiques de la chaîne `str` en minuscules. `LCASE()` est un synonyme de `LOWER()`.
* **Syntaxe** : `LOWER(chaine)` ou `LCASE(chaine)`
* **Exemple** :
```sql
SELECT LOWER('Bonjour le monde'), LCASE('SQL est FANTASTIQUE');
```
| LOWER('Bonjour le monde') | LCASE('SQL est FANTASTIQUE') |
| :------------------------ | :--------------------------- |
| bonjour le monde | sql est fantastique |
> [!tip] Normalisation des Données
> L'utilisation de `UPPER()` ou `LOWER()` est très utile pour normaliser les données textuelles, par exemple pour assurer des comparaisons de chaînes indépendantes de la casse, même si la collation par défaut n'est pas insensible à la casse.
### Fonctions de Concaténation
Ces fonctions permettent de joindre plusieurs chaînes de caractères en une seule chaîne.
* **`CONCAT(str1, str2, ...)`**
* **Description** : Concatène (assemble) une liste de chaînes de caractères en une seule chaîne.
Si l'un des arguments est `NULL`, le résultat de `CONCAT()` est `NULL`.
* **Syntaxe** : `CONCAT(chaine1, chaine2, ...)`
* **Exemple** :
```sql
SELECT
CONCAT('Bonjour', ' ', 'Monde', '!'),
CONCAT('Nom: ', 'Dupont', ', Prénom: ', 'Jean'),
CONCAT('Valeur: ', NULL, '!');
```
| CONCAT('Bonjour', ' ', 'Monde', '!') | CONCAT('Nom: ', 'Dupont', ', Prénom: ', 'Jean') | CONCAT('Valeur: ', NULL, '!') |
| :----------------------------------- | :---------------------------------------------- | :---------------------------- |
| Bonjour Monde! | Nom: Dupont, Prénom: Jean | NULL |
* **`CONCAT_WS(separator, str1, str2, ...)`**
* **Description** : Concatène une liste de chaînes de caractères en utilisant un `separator` spécifié. Le séparateur est ajouté *entre* chaque chaîne non-`NULL`.
Contrairement à `CONCAT()`, `CONCAT_WS()` **ignore les valeurs `NULL`** dans la liste des chaînes à concaténer (mais pas si le `separator` lui-même est `NULL`).
* **Syntaxe** : `CONCAT_WS(separateur, chaine1, chaine2, ...)`
* **Exemple** :
```sql
SELECT
CONCAT_WS('-', '2023', '10', '26'),
CONCAT_WS(', ', 'Pomme', 'Poire', 'Banane'),
CONCAT_WS(' ', 'Dr.', 'Jean', NULL, 'Dupont'),
CONCAT_WS(NULL, 'A', 'B');
```
| CONCAT_WS('-', '2023', '10', '26') | CONCAT_WS(', ', 'Pomme', 'Poire', 'Banane') | CONCAT_WS(' ', 'Dr.', 'Jean', NULL, 'Dupont') | CONCAT_WS(NULL, 'A', 'B') |
| :--------------------------------- | :------------------------------------------ | :-------------------------------------------- | :------------------------ |
| 2023-10-26 | Pomme, Poire, Banane | Dr. Jean Dupont | NULL |
> [!tip] Cas d'utilisation
> `CONCAT_WS()` est particulièrement utile pour construire des adresses complètes, des dates formatées ou des listes d'éléments, en gérant élégamment les champs optionnels qui pourraient être `NULL`.
### Fonctions de Sous-chaînes
Ces fonctions permettent d'extraire des parties spécifiques d'une chaîne de caractères.
* **`SUBSTRING(str, pos, len)` ou `SUBSTR(str, pos, len)`**
* **Description** : Extrait une sous-chaîne de `str`.
* Si `len` est fourni, l'extraction commence à `pos` sur une longueur de `len` caractères.
* Si `len` est omis, l'extraction commence à `pos` et va jusqu'à la fin de la chaîne.
* `pos` peut être positif (début à partir du début de la chaîne, 1-indexé) ou négatif (début à partir de la fin de la chaîne, -1-indexé).
* **Syntaxe** :
* `SUBSTRING(chaine, position_debut, longueur)`
* `SUBSTRING(chaine, position_debut)`
* **Exemple** :
```sql
SELECT
SUBSTRING('Programmation', 1, 4), -- 'Prog'
SUBSTRING('Programmation', 5), -- 'rammation'
SUBSTRING('Programmation', -5), -- 'ation' (les 5 derniers caractères)
SUBSTRING('Programmation', -5, 2); -- 'at' (2 caractères à partir du 5ème en partant de la fin)
```
| SUBSTRING('Programmation', 1, 4) | SUBSTRING('Programmation', 5) | SUBSTRING('Programmation', -5) | SUBSTRING('Programmation', -5, 2) |
| :------------------------------- | :---------------------------- | :----------------------------- | :-------------------------------- |
| Prog | rammation | ation | at |
> [!warning] 1-indexé
> Il est important de se rappeler que MySQL utilise une indexation basée sur 1 pour les positions dans les chaînes, contrairement à de nombreux langages de programmation qui utilisent une indexation basée sur 0.
* **`LEFT(str, len)`**
* **Description** : Extrait les `len` caractères les plus à gauche de la chaîne `str`.
* **Syntaxe** : `LEFT(chaine, longueur)`
* **Exemple** :
```sql
SELECT LEFT('Base de Données', 4), LEFT('Programmation', 0), LEFT('SQL', 10);
```
| LEFT('Base de Données', 4) | LEFT('Programmation', 0) | LEFT('SQL', 10) |
| :------------------------- | :----------------------- | :-------------- |
| Base | | SQL |
> [!note] Comportement des limites
> Si `len` est 0, la fonction retourne une chaîne vide. Si `len` est supérieur à la longueur de la chaîne, la fonction retourne la chaîne entière.
* **`RIGHT(str, len)`**
* **Description** : Extrait les `len` caractères les plus à droite de la chaîne `str`.
* **Syntaxe** : `RIGHT(chaine, longueur)`
* **Exemple** :
```sql
SELECT RIGHT('Base de Données', 7), RIGHT('Programmation', 0), RIGHT('SQL', 10);
```
| RIGHT('Base de Données', 7) | RIGHT('Programmation', 0) | RIGHT('SQL', 10) |
| :-------------------------- | :------------------------ | :--------------- |
| Données | | SQL |
> [!note] Comportement des limites
> Similaire à `LEFT()`, si `len` est 0, la fonction retourne une chaîne vide. Si `len` est supérieur à la longueur de la chaîne, la fonction retourne la chaîne entière.
### Fonctions de Nettoyage (Trimming)
Ces fonctions suppriment les espaces ou d'autres caractères indésirables des extrémités d'une chaîne.
* **`TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)`**
* **Description** : Supprime les caractères spécifiés par `remstr` (par défaut, les espaces) des extrémités de la chaîne `str`.
* `BOTH` (par défaut) : supprime des deux côtés (début et fin).
* `LEADING` : supprime uniquement du début de la chaîne.
* `TRAILING` : supprime uniquement de la fin de la chaîne.
* Si `remstr` est omis, les espaces sont supprimés.
* **Syntaxe** : `TRIM([option] [caractere_a_supprimer] FROM chaine)`
* **Exemple** :
```sql
SELECT
TRIM(' Bonjour Monde '),
TRIM(LEADING 'x' FROM 'xxxBonjourxx'),
TRIM(TRAILING 'x' FROM 'xxxBonjourxx'),
TRIM(BOTH 'x' FROM 'xxxBonjourxx');
```
| TRIM(' Bonjour Monde ') | TRIM(LEADING 'x' FROM 'xxxBonjourxx') | TRIM(TRAILING 'x' FROM 'xxxBonjourxx') | TRIM(BOTH 'x' FROM 'xxxBonjourxx') |
| :-------------------------- | :------------------------------------ | :------------------------------------- | :--------------------------------- |
| Bonjour Monde | Bonjourxx | xxxBonjour | Bonjour |
* **`LTRIM(str)`**
* **Description** : Supprime les espaces (ou d'autres caractères spécifiés par `TRIM(LEADING ... FROM str)`) du début de la chaîne `str`.
* **Syntaxe** : `LTRIM(chaine)`
* **Exemple** :
```sql
SELECT LTRIM(' Bonjour Monde '), LTRIM(' SQL');
```
| LTRIM(' Bonjour Monde ') | LTRIM(' SQL') |
| :--------------------------- | :--------------- |
| Bonjour Monde | SQL |
* **`RTRIM(str)`**
* **Description** : Supprime les espaces (ou d'autres caractères spécifiés par `TRIM(TRAILING ... FROM str)`) de la fin de la chaîne `str`.
* **Syntaxe** : `RTRIM(chaine)`
* **Exemple** :
```sql
SELECT RTRIM(' Bonjour Monde '), RTRIM('SQL ');
```
| RTRIM(' Bonjour Monde ') | RTRIM('SQL ') |
| :--------------------------- | :-------------- |
| Bonjour Monde | SQL |
> [!tip] Nettoyage des données d'entrée
> Ces fonctions sont essentielles pour nettoyer les données textuelles, en particulier celles provenant d'entrées utilisateur ou de sources externes, qui peuvent contenir des espaces superflus et nuire à la cohérence des données.
### Fonctions de Recherche et Remplacement
Ces fonctions sont essentielles pour trouver des motifs spécifiques ou substituer des parties de chaînes.
* **`REPLACE(str, from_str, to_str)`**
* **Description** : Remplace toutes les occurrences de la sous-chaîne `from_str` par `to_str` dans la chaîne `str`.
La recherche est sensible à la casse selon la collation de la chaîne.
* **Syntaxe** : `REPLACE(chaine, chaine_a_remplacer, chaine_de_remplacement)`
* **Exemple** :
```sql
SELECT
REPLACE('www.exemple.com', 'www', 'ftp'),
REPLACE('Ceci est un test, un test', 'test', 'exercice');
```
| REPLACE('www.exemple.com', 'www', 'ftp') | REPLACE('Ceci est un test, un test', 'test', 'exercice') |
| :--------------------------------------- | :------------------------------------------------------- |
| ftp.exemple.com | Ceci est un exercice, un exercice |
* **`INSTR(str, substr)`**
* **Description** : Retourne la position (1-indexée) de la première occurrence de `substr` dans `str`.
Retourne `0` si `substr` n'est pas trouvée.
* **Syntaxe** : `INSTR(chaine_principale, sous_chaine_a_chercher)`
* **Exemple** :
```sql
SELECT
INSTR('Bonjour le monde', 'monde'),
INSTR('Bonjour le monde', 'SQL');
```
| INSTR('Bonjour le monde', 'monde') | INSTR('Bonjour le monde', 'SQL') |
| :------------------------------- | :------------------------------- |
| 12 | 0 |
* **`LOCATE(substr, str)` ou `LOCATE(substr, str, pos)`**
* **Description** : Identique à `INSTR()`, mais l'ordre des arguments est inversé.
L'argument `pos` est optionnel et spécifie la position (1-indexée) à partir de laquelle la recherche doit commencer dans `str`.
* **Syntaxe** :
* `LOCATE(sous_chaine_a_chercher, chaine_principale)`
* `LOCATE(sous_chaine_a_chercher, chaine_principale, position_depart)`
* **Exemple** :
```sql
SELECT
LOCATE('monde', 'Bonjour le monde'),
LOCATE('o', 'Bonjour le monde', 3),
LOCATE('SQL', 'Bonjour le monde');
```
| LOCATE('monde', 'Bonjour le monde') | LOCATE('o', 'Bonjour le monde', 3) | LOCATE('SQL', 'Bonjour le monde') |
| :---------------------------------- | :------------------------------- | :-------------------------------- |
| 12 | 7 | 0 |
> [!note] `INSTR` vs `LOCATE`
> La principale différence est l'ordre des arguments. `LOCATE` offre en plus la flexibilité de spécifier une position de départ pour la recherche, ce qui peut être utile pour trouver des occurrences multiples.
## Les Fonctions Conditionnelles
### Remplacement de `NULL`
* **`IFNULL(expr1, expr2)`**
* **Description** : Retourne `expr1` si `expr1` n'est pas `NULL`. Sinon, elle retourne `expr2`. C'est un raccourci pratique pour les cas où une seule alternative à `NULL` est nécessaire.
* **Syntaxe** : `IFNULL(expression_a_tester, valeur_si_null)`
* **Exemple** :
```sql
SELECT
IFNULL(10, 0),
IFNULL(NULL, 0),
IFNULL('Bonjour', 'Salut');
```
| IFNULL(10, 0) | IFNULL(NULL, 0) | IFNULL('Bonjour', 'Salut') |
| :------------ | :-------------- | :------------------------- |
| 10 | 0 | Bonjour |
> [!example] Cas d'usage de `IFNULL()`
> Reprenons l'exemple des produits. Si le `PrixRemise` est `NULL`, nous voulons afficher le `PrixStandard`.
>
> ```sql
> SELECT
> NomProduit,
> IFNULL(PrixRemise, PrixStandard) AS PrixFinal
> FROM Produits;
> ```
* **`COALESCE(expr1, expr2, ..., exprN)`**
* **Description** : Retourne la première expression de sa liste qui n'est pas `NULL`. Si toutes les expressions sont `NULL`, la fonction retourne `NULL`. Elle est souvent utilisée pour fournir une valeur par défaut lorsqu'une colonne peut être `NULL`.
* **Syntaxe** : `COALESCE(expression1, expression2, ..., expressionN)`
* **Exemple** :
```sql
SELECT
COALESCE(NULL, NULL, 5, 10),
COALESCE(NULL, 'Valeur par défaut'),
COALESCE(1, NULL, 2);
```
| COALESCE(NULL, NULL, 5, 10) | COALESCE(NULL, 'Valeur par défaut') | COALESCE(1, NULL, 2) |
| :-------------------------- | :---------------------------------- | :------------------- |
| 5 | Valeur par défaut | 1 |
> [!example] Cas d'usage de `COALESCE()`
> Une table `Clients` a des colonnes `EmailPrincipal`, `EmailSecondaire`, `EmailContactUrgence`. Nous voulons afficher une adresse e-mail, en privilégiant le principal, sinon le secondaire, sinon l'urgence, sinon "Aucun email fourni".
>
> ```sql
> SELECT
> NomClient,
> COALESCE(EmailPrincipal, EmailSecondaire, EmailContactUrgence, 'Aucun email fourni') AS EmailClient
> FROM Clients;
> ```
> Si `EmailPrincipal` est `NULL`, `COALESCE` essaiera `EmailSecondaire`. Si `EmailSecondaire` est aussi `NULL`, il essaiera `EmailContactUrgence`. Si toutes sont `NULL`, il affichera `'Aucun email fourni'`.
> [!note] Différence entre `COALESCE()` et `IFNULL()`
> - `IFNULL()` est une extension MySQL et ne prend que deux arguments. Elle est généralement plus rapide pour deux arguments.
> - `COALESCE()` est standard ANSI SQL et peut prendre un nombre illimité d'arguments.
> - `COALESCE(A, B)` est fonctionnellement équivalent à `IFNULL(A, B)`.
> - Pour plus de deux arguments, `COALESCE(A, B, C)` est préférable à des `IFNULL()` imbriqués comme `IFNULL(A, IFNULL(B, C))`.
### Condition simple : `IF()`
* **`IF(condition, valeur_si_vrai, valeur_si_faux)`**
* **Description** : Évalue une `condition`. Si la `condition` est `TRUE` (non zéro et non `NULL`), elle retourne `valeur_si_vrai`. Sinon (si la condition est `FALSE`, `0` ou `NULL`), elle retourne `valeur_si_faux`.
* **Syntaxe** : `IF(condition, valeur_si_vrai, valeur_si_faux)`
* **Exemple** :
```sql
SELECT
IF(1 > 0, 'Vrai', 'Faux'),
IF(1 = 0, 'Vrai', 'Faux'),
IF(NULL, 'Vrai', 'Faux');
```
| IF(1 > 0, 'Vrai', 'Faux') | IF(1 = 0, 'Vrai', 'Faux') | IF(NULL, 'Vrai', 'Faux') |
| :------------------------ | :------------------------ | :----------------------- |
| Vrai | Faux | Faux |
> [!example] Cas d'usage de `IF()`
> Dans une table `Etudiants`, nous voulons afficher "Admis" si la `NoteMoyenne` est supérieure ou égale à 10, sinon "Refusé".
>
> ```sql
> SELECT
> NomEtudiant,
> NoteMoyenne,
> IF(NoteMoyenne >= 10, 'Admis', 'Refusé') AS StatutAdmission
> FROM Etudiants;
> ```
### Conditions multiples : L'instruction `CASE`
L'instruction `CASE` est la structure conditionnelle la plus puissante et flexible en SQL, permettant de gérer des logiques complexes avec plusieurs conditions. Elle est une composante essentielle de la norme ANSI SQL. Elle existe sous deux formes principales.
#### Syntaxe 1 : `CASE expression WHEN value THEN result ... END` (Forme Simple)
Cette forme compare une expression unique à plusieurs valeurs possibles.
> [!definition] `CASE expression WHEN value1 THEN result1 [WHEN value2 THEN result2 ...] [ELSE default_result] END`
> L'instruction `CASE` évalue l'`expression` fournie. Elle compare ensuite cette `expression` séquentiellement à chaque `valueN` des clauses `WHEN`. Dès qu'une correspondance est trouvée (`expression = valueN`), elle retourne le `resultN` correspondant et s'arrête. Si aucune correspondance n'est trouvée et qu'une clause `ELSE` est présente, elle retourne `default_result`. Sinon, elle retourne `NULL`.
**Syntaxe :**
```sql
CASE expression
WHEN valeur1 THEN resultat1
WHEN valeur2 THEN resultat2
...
[ELSE resultat_par_defaut]
END
```
> [!example] Exemple de `CASE` (forme simple)
> Nous avons une colonne `CodeStatut` dans une table `Commandes` (1: 'En attente', 2: 'Expédiée', 3: 'Livrée', 4: 'Annulée'). Nous voulons afficher le statut en texte clair.
>
> ```sql
> SELECT
> IDCommande,
> CASE CodeStatut
> WHEN 1 THEN 'En attente'
> WHEN 2 THEN 'Expédiée'
> WHEN 3 THEN 'Livrée'
> WHEN 4 THEN 'Annulée'
> ELSE 'Statut inconnu' -- Pour tout autre code ou NULL
> END AS StatutTexte
> FROM Commandes;
> ```
#### Syntaxe 2 : `CASE WHEN condition THEN result ... END` (Forme Conditionnelle)
Cette forme permet d'évaluer différentes conditions booléennes indépendantes. C'est la plus courante et la plus flexible pour des logiques complexes.
> [!definition] `CASE WHEN condition1 THEN result1 [WHEN condition2 THEN result2 ...] [ELSE default_result] END`
> L'instruction `CASE` évalue les `condition`s séquentiellement. Dès qu'une `condition` est `TRUE`, elle retourne le `resultat` correspondant et s'arrête. Les conditions suivantes ne sont pas évaluées. Si aucune condition n'est `TRUE`, et si une clause `ELSE` est présente, elle retourne `resultat_par_defaut`. Sinon, elle retourne `NULL`.
**Syntaxe :**
```sql
CASE
WHEN condition1 THEN resultat1
WHEN condition2 THEN resultat2
...
[ELSE resultat_par_defaut]
END
```
> [!example] Exemple de `CASE` (forme conditionnelle)
> Calculer une catégorie de prix pour des produits : "Économique" (< 50€), "Standard" (50€-200€), "Premium" (> 200€).
>
> ```sql
> SELECT
> NomProduit,
> PrixUnitaire,
> CASE
> WHEN PrixUnitaire < 50 THEN 'Économique'
> WHEN PrixUnitaire <= 200 THEN 'Standard'
> WHEN PrixUnitaire > 200 THEN 'Premium'
> ELSE 'Prix non défini' -- Gérer les cas où PrixUnitaire est NULL ou négatif, etc.
> END AS CategoriePrix
> FROM Produits;
> ```
> [!tip] Ordre des conditions dans `CASE`
> L'ordre des clauses `WHEN` est crucial dans la forme conditionnelle. La première condition `TRUE` est celle qui est exécutée, et les conditions suivantes sont ignorées. Assurez-vous que vos conditions sont ordonnées logiquement, souvent du plus spécifique au plus général, ou de manière à ce que les conditions mutuellement exclusives soient traitées dans l'ordre souhaité.
---