# Les fonctions spéciales

Il y a plusieurs fonctions spéciales en SQL qui peuvent être ajoutée plus ou moins n'importe où dans la requête.

## Conversion des dates
Pour convertir un `DATE` en chaine de caractère, on peut utilsier `TO_CHAR()`

```sql
SELECT localite, TO_CHAR(date_mise_en_vente, 'month-yyyy') FROM Bien;
```

Pour ce qui est de la syntaxe voici un extrait :

| Syntaxe SQL | Correspond à | Exemple |
| --- | --- | --- | 
| `dd` | Jour du mois | `05` |
| `mm` | Mois en numéro | `02` |
| `yyyy` | Année en 4 numéros | `2022` |
| `yy` | Année en 2 numéros | `22` | 
| `month` | Mois (épellé) | `décembre` |
| `mon` | Nom du mois en abrégé | `JAN` | 
| `ddd` | Jour de l'année | `365` |
| `day` | Le jour en lettres | `lundi` |
| `mi` | Minutes | `56` | 
| `hh` | Heures en format 12h | `1` |
| `ss` | Secondes | `59` |
| `hh24` | Heures en format 24h | `13` | 
| `am` | Afficher AM si matin ou PM si après midi | `PM` |

> Pour la liste complète du format de TO_CHAR [cliquez ici](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Format-Models.html#GUID-EAB212CF-C525-4ED8-9D3F-C76D08EEBC7A)

Le même principe peut être utilisé pour convertir des chaines de caractères en date aussi avec `TO_DATE()` qui a la même syntaxe. 

- Si on veut juste extraire sans formatter certaines valeurs de `DATE` ou `TIME` on peut utiliser la fonction `EXTRACT()`

```sql
-- YEAR peut être remplacé par MONTH, DAY. Et dans les cas où c'est `TIME` également par HOUR, MINUTE ou SECOND
SELECT EXTRACT(YEAR FROM date_mise_en_vente) AS annee FROM Bien;
-- OUTPUT: 2012
```

- On peut aussi récupérer la date actuelle du système avec la fonction `SYSDATE()`

```sql
-- On ajoute dans une table 'Leaderboard' la date avec SYSDATE
INSERT INTO Leaderboard (date, nom, prenom, score) VALUES (SYSDATE, 'Roger', 'Pierre', 144);
```

- On peut arrondir une date à une certaine unité avec `ROUND()`

```sql
-- On va arondir la date au mois ce qui va retourner des choses tel que '01-12-2022' à la place de '13-12-2022'
SELECT ROUND(date_mise_en_vente, 'month') FROM Bien;
--                               \_ Les valeurs ici sont les même que celle vue dans le tableau au dessus
```

- On peut ajouter un certain nombre de mois à une date avec `ADD_MONTHS()`

```sql
-- Affiche la date d'ajourd'hui, la date d'aujourd'hui + 5 mois et le nombre de jours entre les 2
SELECT SYSDATE AS aujourdhui, 
    ADD_MONTHS(SYSDATE, 5) AS deadline, 
    ADD_MONTHS(SYSDATE, 5) - SYSDATE AS tempsrestant  
FROM Bien;
-- OUTPUT: AUJOURDH DEADLINE JOURSRESTANTS
--         -------- -------- -------------
--         30/12/22 30/05/23           151
```

- Affiche le dernier jour du mois avec `LAST_DAY()`

```sql
SELECT SYSDATE AS aujourdhui, LAST_DAY(SYSDATE) AS findumois
FROM Bien;
-- OUTPUT: AUJOURDH FINDUMOI
--         -------- --------
--         30/12/22 31/12/22
```

- Affiche le nombre de mois de différence entre deux dates avec `MONTHS_BETWEEN()`

```sql
-- Ici je vais reprendre un exemple précédent mais changer le 'tempsrestant'
SELECT SYSDATE AS aujourdhui, 
    ADD_MONTHS(SYSDATE, 5) AS deadline, 
    MONTHS_BETWEEN(ADD_MONTHS(SYSDATE, 5), SYSDATE) AS tempsrestant
FROM Bien;
-- OUTPUT: AUJOURDH DEADLINE TEMPSRESTANT
--         -------- -------- ------------
--         30/12/22 30/05/23            5
```

- Pour avoir la date du premier jour de la semaine donné à partir d'une date on peut utiliser `NEXT_DAY()`

```sql
-- Donne la date du prochain lundi à partir d'aujourd'hui
SELECT SYSDATE AS aujourdhui,
    NEXT_DAY(SYSDATE, 'LUNDI') AS prochainlundi
FROM Bien;
-- OUTPUT: AUJOURDH PROCHAIN
--         -------- --------
--         30/12/22 02/01/23
```

## Conversion des chaines de caractères
On peut utiliser `LOWER()` pour mettre en minuscule, ou `UPPER()` pour mettre en majuscule.

```sql
-- Voici un exemple de SQL avec un LIKE qui est case-insensitive en le transformant en lowercase
SELECT * FROM Bien WHERE LOWER(description) = '%calme%';
```

Enfin on peut aussi utiliser `SUBSTR()` pour couper une chaine de caractère (c'est très similaire au *substring* en Java)

```sql
-- Va donner l'initiale du prenom + le nom des candidats et renomer la colonne 'candidat'
-- On donne ici la position de début (1) et la position de fin (1) dans la chaine. 
-- Mais si on veut prendre tout à partir de la position 2 par exemple, on peut juste écrire SUBSTR(nom, 2)
SELECT SUBSTR(prenom, 1, 1) || '. ' || nom AS candidat FROM Candidat;
```

On peut utiliser la fonction replace pour remplacer une chaine par une autre

```sql
-- Remplace tous les "robert" par des "roger"
SELECT REPLACE(prenom, 'Robert', 'Roger') AS prenom FROM candidat;
```

Mais dans certains cas on veut éliminer toute une série de caractère de nos entrées (pour pouvoir les utiliser comme nom de fichier par exemple) en utilisant `TRANSLATE`

```sql
-- Remplace les caractères espace, apostrophe, point, slash, backslash et étoile par des underscore
SELECT TRANSLATE(nom, ' ''"./\*', '_______') AS filename FROM fichiers;
```

Pour savoir la longueur d'une chaine de caractère on peut utiliser la fonction `LENGTH`

```sql
-- Donne pour chaque candidat, son nom et la longueur de son nom
SELECT nom, LENGTH(nom) AS longueurdunom FROM candidat;
```

Pour savoir la position d'une chaine de caractère dans une autre chaine, on peut utiliser `INSTR`

```sql
-- L'exemple ici va donner 14.
-- Il va rechercher la 2e occurence de OR dans la chaine à partir du 3e caractère
SELECT INSTR('CORPORATE FLOOR','OR', 3, 2) FROM Bien;
--            |                |     |  \_Quelle occurence
--            |                |     \_A partir de X caractère
--            \_Input          \_Chaine à rechercher                
```

Pour simplement retirer des caractères, sans utiliser `REPLACE` on peut utiliser `TRIM` et ses variantes

```sql
-- Pour remplacer le début d'une chaine de caractères
SELECT LTRIM('foo bar', 'foo ') FROM Bien;
-- OUTPUT: 'bar'

-- Pour remplacer la fin d'une chaine de caractères
SELECT RTRIM('foo bar', ' bar') FROM Bien;
-- OUTPUT: 'foo'

-- Pour remplacer le début et la fin d'une chaine de caractères
-- ATTENTION: TRIM ne fonctionne que pour 1 caractère
SELECT TRIM(BOTH 'o' FROM 'oobaroo') FROM Bien;
-- OUTPUT: 'bar'

-- Note: 'BOTH' peut être remplacé par LEADING pour isoler le début ou par TRAILING pour isoler la fin
```

On peut convertir une chaine de caractère en nombre avec `TO_NUMBER`

```sql
SELECT TO_NUMBER('1210.73', '9999.99') FROM Bien;
```

## Gestion des nombres
- La fonction `ROUND()` fonctionne également pour les nombres

```sql
-- Ceci donne le nombre de jour qui sépare la date de mise en vente de chaque bien de la date d'aujourd'hui
-- Ce nombre est normalement un nombre à virgule mais on l'arrondi avec ROUND
SELECT ROUND(SYSDATE - date_mise_en_vente) AS nbr_jours
FROM BIEN;
-- OUTPUT:  NBR_JOURS
--         ----------
--               3858
```

## En savoir plus
- [En savoir plus sur les fonctions dans Oracle SQL](https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/About-SQL-Functions.html)
- [Voir tous les formats de TO_CHAR et TO_DATE](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Format-Models.html)
- [Voir tous les formats de ROUND et TRUNC](https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/ROUND-and-TRUNC-Date-Functions.html)