Les fonctions

1. ABS : valeur absolue

Exemple

SELECT idTarif, DateDebut, Prix, ABS(Prix) AS ValeurAbsolue FROM Tarifs; 

2. Valeur ASCII d’un caractère

Dans cet exemple, on va afficher le code ASCII du premier caractère de la colonne Libelle des hôtels :

SQL Server, MySQL et PostgreSQL

SELECT idHotel, Libelle, ASCII(SUBSTRING(Libelle, 1, 1)) AS Code 
FROM Hotels; 

Oracle, MySQL et PostgreSQL

SELECT idHotel, Libelle, ASCII(SUBSTR(Libelle, 1, 1)) AS Code 
FROM Hotels; 

3. COS : cosinus - SIN : sinus

Dans cet exemple, on va afficher le cosinus du prix de la table Tarifs.

Exemple

SELECT idTarif, DateDebut, Prix, COS(Prix) AS Cosinus FROM Tarifs; 

Pour obtenir le sinus, c’est la même syntaxe :

SELECT idTarif, DateDebut, Prix, SIN(Prix) AS Sinus FROM Tarifs; 

4. LOG (<numéro base>,<colonne>) : logarithme de la colonne sélectionnée dans la base indiquée

Dans cet exemple, on va afficher le logarithme en base 2 des tarifs.

Exemple

SELECT idTarif, DateDebut, Prix, LOG(2, Prix) AS Log FROM Tarifs; 

5. MOD(<colonne>,<valeur>) : modulo

Le modulo donne le reste de la division d’une colonne par une valeur. Dans cet exemple, on va afficher le modulo du prix divisé par 4.

Oracle, MySQL et PostgreSQL

SELECT idTarif, DateDebut, Prix, MOD(Prix, 4) AS Modulo FROM Tarifs; 

1. Date du jour : CURRENT_DATE

Pour Oracle, il existe CURRENT_DATE et SYSDATE. Pour MySQL, on peut utiliser CURRENT_DATE, CURDATE, NOW. Pour SQL Server, c’est GETDATE() et SYSDATETIME().

Ces fonctions permettent de retourner la date du jour, avec des précisions différentes (millisecondes, nanosecondes…). En fonction du SGBDR, il existe différentes fonctions qui réalisent la même chose et qui sont synonymes.

Petite particularité pour Oracle et SQL Server, SYSDATE et SYSDATETIME() donnent la date du serveur sur lequel est installée la base de données et CURRENT_DATE ou GETDATE() donnent la date de la session de l’utilisateur.

Par exemple, si la base est installée sur un serveur américain et que l’utilisateur est en France, il y aura une différence de 6 heures entre les deux dates.

Oracle

SELECT CURRENT_DATE, SYSDATE FROM DUAL;  

MySQL

SELECT CURRENT_DATE,CURRENT_DATE(), CURDATE(), NOW() FROM 
DUAL; 

SQL Server

SELECT GETDATE() AS dateSession, GETUTCDATE() AS dateUTCSession, 
CURRENT_TIMESTAMP AS dateSession, SYSDATETIME() AS dateSystème, 
SYSUTCDATETIME() AS dateUTCSystème, SYSDATETIMEOFFSET() AS 
dateOffsetSystème; 

PostgreSQL

SELECT CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP, now(),  
transaction_timestamp(), statement_timestamp(), clock_timestamp(), timeofday(); 

Dans MySQL, SQL Server et PostgreSQL, le () signifie qu’il s’agit d’une fonction. S’il n’y a pas de parenthèses, c’est une variable.

Il existe quelques différences d’affichage entre ces fonctions et variables. NOW par exemple affiche la date et l’heure courante et les autres uniquement la date pour MySQL. L’affichage écran de ces fonctions de type date est aussi dépendant des options et paramètres utilisés pour la base de données (NLS_DATE_FORMAT pour Oracle et dateformat pour SQL Server).

Nous avons déjà vu qu’il est préférable de fournir dans...

1. Changement de casse LOWER / UPPER / UCASE / LCASE (minuscules et majuscules)

Il existe des fonctions pour mettre les chaînes de caractères en minuscules ou en majuscules. Les noms différent là encore entre chaque SGBD.

Sous Oracle et SQL Server, on utilisera LOWER et UPPER. Sous MySQL, LOWER, UPPER, UCASE et LCASE sont autorisés.

LCASE et LOWER sont utilisés pour mettre en minuscules et UCASE et UPPER sont utilisés pour mettre en majuscules.

Exemples pour Oracle

SELECT LOWER('Ceci est un TEST') AS MINUSCULE FROM DUAL; 
 
MINUSCULE 
---------------- 
ceci est un test 
 
SELECT UPPER('Ceci est un TEST') AS MAJUSCULE FROM DUAL; 
 
MAJUSCULE 
---------------- 
CECI EST UN TEST 

Exemples pour MySQL

SELECT LCASE('Ceci est un TEST') AS MINUSCULE 
 
+------------------+ 
| MINUSCULE        | 
+------------------+ 
| ceci est un test | 
+------------------+ 
 
SELECT UCASE('Ceci est un TEST') AS MAJUSCULE 
 
+------------------+ 
| MAJUSCULE        | 
+------------------+ 
| CECI EST UN TEST | 
+------------------+ 

Exemples pour SQL Server et PostgreSQL

SELECT LOWER('Ceci est un TEST') AS MINUSCULE;  
  
MINUSCULE  
----------------  
ceci est un test  
  
SELECT UPPER('Ceci est un TEST') AS MAJUSCULE;  
  
MAJUSCULE  
----------------  
CECI EST UN TEST 

La syntaxe est donc :

SELECT LOWER ou UPPER (<colonne ou variable>) ... FROM <table1>, 
<table2> ... 

avec la possibilité d’utiliser LCASE et UCASE en MySQL.

2. Supprimer les espaces à droite ou à gauche d’une chaîne de caractères : TRIM / LTRIM / RTRIM

Pour supprimer des espaces dans une chaîne de caractères, il faut utiliser soit LTRIM pour supprimer les espaces à gauche (Left) ou RTRIM pour supprimer les espaces à droite (Right). Pour supprimer les espaces à droite et à gauche, il existe TRIM.

Exemples pour Oracle et PostgreSQL

SELECT '*'||LTRIM('   SUPPRESSION DES ESPACES A GAUCHE   ')||'*' AS 
SUPG...

1. Transformer un numérique ou une date en texte : TO_CHAR

Ce sont les fonctions TO_CHAR sous Oracle et CAST sous MySQL qui permettent de convertir un numérique ou une date en caractères.

Exemple pour Oracle

SELECT TO_CHAR(PRIX) PRIX, TO_CHAR(DATEDEBUT,'DD/MM/YY') DateDebut 
FROM Tarifs; 

Exemple pour PostgreSQL, MySQL et SQL Server

SELECT CAST(Prix as char(6)) AS Prix, CAST(DateDebut as char(10)) 
AS DateDebut FROM Tarifs; 

Autre exemple pour SQL Server

SELECT CONVERT(char(6), Prix) AS Prix, CONVERT(char(10), 
DateDebut) AS DateDebut FROM Tarifs; 

La syntaxe est donc pour Oracle :

SELECT TO_CHAR(<colonne ou variable>,[<FORMAT>],<chaine  
recherchée> ...  FROM <table1>, <table2> ... 

La syntaxe est donc pour PostgreSQL, MySQL et SQL Server :

SELECT CAST(<colonne ou variable> as <TYPE>) ...  FROM <table1>, 
<table2> ... 

Une autre syntaxe pour SQL Server :

SELECT CONVERT(<TYPE>, <colonne ou variable>) ...  FROM <table1>,  
<table2> ... 

2. Changer le type d’une colonne : CAST ou CONVERT

Ces fonctions permettent de changer le type d’une colonne le temps de l’ordre SQL, par exemple changer une colonne qui est initialement en VARCHAR en INTEGER afin de faire un calcul ou tester la valeur...

1. Numérotation séquentielle et rangement de lignes

Ce sont les fonctions qui permettent de ranger des lignes. RANK permet de numéroter des lignes dans un ordre. DENSE_RANK permet de numéroter les lignes dans un ordre avec des numéros à suivre. ROW_NUMBER permet de numéroter des lignes avec des numéros à suivre, telle une suite. La numérotation peut être effectuée sur des sous-groupes d’enregistrements.

Voici des exemples de numérotation de la table tarifs par prix, sans et avec partitionnement par type de chambre.

Exemples pour SQL Server, Oracle et PostgreSQL

SELECT Hotel, typeChambre, Prix, RANK() OVER(ORDER BY Prix DESC) 
AS Numero, DENSE_RANK() OVER(ORDER BY Prix DESC) AS NumeroASuivre, 
ROW_NUMBER() OVER(ORDER BY Prix DESC) AS Suite FROM Tarifs; 

SELECT Hotel, typeChambre, Prix 
, RANK() OVER(PARTITION BY TypeChambre ORDER BY Prix DESC) AS Numero 
, DENSE_RANK() OVER(PARTITION BY TypeChambre ORDER BY Prix DESC) AS 
NumeroASuivre 
, ROW_NUMBER() OVER(PARTITION BY TypeChambre ORDER BY Prix DESC) AS Suite 
FROM Tarifs; 

Dans cet exemple, il y a deux types de chambres que l’on classe par prix. Le premier type a trois prix différents. On voit la différence entre le Numero et NumeroASuivre sur le troisième prix où le numéro (fonction RANK) reprend le positionnement du tarif c’est-à-dire 6, alors que NumeroAsuivre (fonction DENSE_RANK) continue la numérotation. La suite est basée sur un pas de 1. La numérotation reprend à 1 sur le second type de chambre, car le partitionnement est basé sur le champ TypeChambre.

2. Distribution de lignes en groupes numérotés

La fonction NTILE permet de distribuer et numéroter des lignes dans un nombre...

1. NVL : tester une colonne à null

Le NVL pour « Null Value » permet de savoir si une colonne est renseignée ou pas et de lui attribuer une valeur dans le cas où elle est à null. La fonction correspondante dans SQL Server est ISNULL.

Syntaxe

SELECT NVL(<nom colonne>,<valeur attribuée>), ... 

Exemple Oracle

SELECT NumChambre, Commentaire, NVL(Commentaire, 'Vue sur le jardin') 
AS Commentaires FROM Chambres; 

Exemple SQL Server

SELECT NumChambre, Commentaire, ISNULL(Commentaire, 'Vue sur le jardin') 
AS Commentaires FROM Chambres; 

Résultat

On constate que les chambres qui ont la colonne Commentaire à NULL sont remplacées par la valeur « Vue sur le jardin ». La seconde ligne contient un espace, qui n’est pas considéré comme NULL.

2. Tester plusieurs valeurs : COALESCE

Cette fonction permet de tester plusieurs valeurs NULL de colonnes sur une même fonction, évitant ainsi de faire des tests avec des « IF » « THEN », etc.

Elle teste chaque valeur de colonne puis attribue le résultat de gauche à droite. La première colonne non NULL est attribuée. Si toutes les colonnes sont NULL, la fonction prendra la valeur par défaut qui doit être le dernier argument.

Syntaxe

COALESCE(<colonne1>, <colonne 2>,... <valeur par défaut>); 

Exemple

Si on a dans la table Tarifs les données suivantes :