Fonctions scalaires

• Rappels et introduction
• Sommaire
• Fonctions d'agrégation

Comme prévu, ce chapitre sera consacré aux fonctions scalaires permettant la manipulation de nombres et de chaînes de caractères. Nous verrons entre autres comment arrondir un nombre ou tirer un nombre au hasard, et comment connaître la longueur d'un texte ou en extraire une partie. La fin de ce chapitre est constituée d'exercices afin de pouvoir mettre les fonctions en pratique.

À nouveau, si vous ne trouvez pas votre bonheur parmi les fonctions présentées ici, n'hésitez surtout pas à faire un tour sur la documentation officielle. N'essayez pas de retenir par cœur toutes ces fonctions bien sûr. Si vous savez qu'elles existent, il vous sera facile de retrouver leur syntaxe exacte en cas de besoin, que ce soit ici ou dans la documentation.

• Manipulation de nombres
• Manipulation de chaînes de caractères
• Exemples d'application et exercices

Manipulation de nombres

Voici donc quelques fonctions scalaires qui vont vous permettre de manipuler les nombres : faire des calculs, des arrondis, prendre un nombre au hasard, etc.

Arrondis

Arrondir un nombre, c'est trouver une valeur proche de ce nombre avec une précision donnée et selon certains critères. La précision est en général représentée par le nombre de décimales désirées. Par exemple, pour un prix, on travaille rarement avec plus de deux décimales. Pour un âge, on préférera généralement un nombre entier (c'est-à-dire aucune décimale). Quant aux critères, il s'agit de décider si l'on veut arrondir au plus proche (ex. : 4,3 arrondi à l'entier le plus proche vaut 4), arrondir au supérieur (ex. : 4,3 arrondi à l'entier supérieur vaut 5) ou arrondir à l'inférieur (ex. : 4,3 arrondi à l'entier inférieur vaut 4).

Voici quatre fonctions permettant d'arrondir les nombres, selon ces différents critères. Pour les paramètres, n représente le nombre à arrondir, d le nombre de décimales désirées.

CEIL()

CEIL(n) ou CEILING(n) arrondit au nombre entier supérieur.

1

SELECT CEIL(3.2), CEIL(3.7);

FLOOR()

FLOOR(n) arrondit au nombre entier inférieur.

1

SELECT FLOOR(3.2), FLOOR(3.7);

ROUND()

ROUND(n, d) arrondit au nombre à d décimales le plus proche. ROUND(n) équivaut à écrire ROUND(n, 0), donc arrondit à l'entier le plus proche.

1
2
3

SELECT ROUND(3.22, 1), ROUND(3.55, 1), ROUND(3.77, 1);

SELECT ROUND(3.2), ROUND(3.5), ROUND(3.7);

Si le nombre se trouve juste entre l'arrondi supérieur et l'arrondi inférieur (par exemple si on arrondit 3,5 à un nombre entier), certaines implémentations vont arrondir vers le haut, d'autres vers le bas. Testez donc pour savoir dans quel cas est votre serveur (comme vous voyez, ici, c'est vers le haut).

TRUNCATE()

TRUNCATE(n, d) arrondit en enlevant purement et simplement les décimales en trop (donc arrondi à l'inférieur pour les nombres positifs, au supérieur pour les nombres négatifs).

1
2
3

SELECT TRUNCATE(3.2, 0), TRUNCATE(3.5, 0), TRUNCATE(3.7, 0);

SELECT TRUNCATE(3.22, 1), TRUNCATE(3.55, 1), TRUNCATE(3.77, 1);

Exposants et racines

Exposants

POWER(n, e) (ou POW(n, e)) retourne le résultat de n exposant e ($n^e$).

Pour rappel, n exposant e ($n^e$) veut dire que l'on multiplie n par lui-même, e fois. Donc par exemple, $2^3 = 2\times2\times2 = 8$

1

SELECT POW(2, 5), POWER(5, 2);

Racines

Prendre la racine n^ième d'un nombre x ($\sqrt[n]{x}$), c'est trouver le (ou les) nombre(s) y qui répond(ent) à la condition suivante : $y^n = x$. Donc la racine cinquième de 32 ($\sqrt[5]{32}$) vaut 2, puisque $2^5 = 2\times2\times2\times2\times2 = 32$

SQRT(n) donne la racine carrée positive de n ($\sqrt{n} = \sqrt[2]{n}$).

1

SELECT SQRT(4);

Il n'existe pas de fonction particulière pour obtenir la racine n^ième d'un nombre pour n > 2. Cependant, pour ceux qui ne le sauraient pas : $\sqrt[n]{x} = x^{\frac{1}{n}}$. Donc, pour obtenir par exemple la racine cinquième de 32, il suffit de faire :

1

SELECT POW(32, 1/5);

Hasard

Le "vrai" hasard n'existe pas en informatique. Il est cependant possible de simuler le hasard, avec par exemple un générateur de nombres aléatoires. MySQL implémente un générateur de nombres aléatoires auquel vous pouvez faire appel en utilisant la fonction RAND(), qui retourne un nombre "aléatoire" entre 0 et 1.

1

SELECT RAND();

Cette fonction peut par exemple être utile pour trier des résultats de manière aléatoire. Un nombre différent va en effet être généré pour chaque ligne de résultat. Le tri se fera alors sur ce nombre généré "au hasard". Notez que ce n'est pas très bon en termes de performances, étant donné qu'un nombre doit être généré pour chaque ligne.

1
2
3

SELECT * 
FROM Race 
ORDER BY RAND();

Divers

SIGN()

SIGN(n) renvoie le signe du nombre n. Ou plus exactement, SIGN(n) renvoie -1 si n est négatif, 0 si n vaut 0, et 1 si n est positif.

1

SELECT SIGN(-43), SIGN(0), SIGN(37);

ABS()

ABS(n) retourne la valeur absolue de n, donc sa valeur sans le signe.

1

SELECT ABS(-43), ABS(0), ABS(37);

MOD()

La fonction MOD(n, div) retourne le modulo, donc le reste de la division entière de n par div (comme l'opérateur % ou MOD)

1

SELECT MOD(56, 10);

Manipulation de chaînes de caractères

Nous allons maintenant manipuler, triturer, examiner des chaînes de caractères. À toutes fins utiles, je rappelle en passant qu'en SQL, les chaînes de caractères sont entourées de guillemets (simples, mais les guillemets doubles fonctionnent avec MySQL également).

Longueur et comparaison

Connaître la longueur d'une chaîne

Trois fonctions permettent d'avoir des informations sur la longueur d'une chaîne de caractères. Chaque fonction calcule la longueur d'une manière particulière.

• BIT_LENGTH(chaine) : retourne le nombre de bits de la chaîne. Chaque caractère est encodé sur un ou plusieurs octets, chaque octet représente huit bits.
• CHAR_LENGTH(chaine) : (ou CHARACTER_LENGTH()) retourne le nombre de caractères de la chaîne.
• LENGTH(chaine) : retourne le nombre d'octets de la chaîne. Chaque caractère étant représenté par un ou plusieurs octets (rappel : les caractères que vous pouvez utiliser dépendent de l'encodage choisi).

1
2
3

SELECT BIT_LENGTH('élevage'), 
       CHAR_LENGTH('élevage'), 
       LENGTH('élevage'); -- Les caractères accentués sont codés sur 2 octets en UTF-8

A priori, dans neuf cas sur dix au moins, la fonction qui vous sera utile est donc CHAR_LENGTH().

Comparer deux chaînes

La fonction STRCMP(chaine1, chaine2) compare les deux chaînes passées en paramètres et retourne 0 si les chaînes sont les mêmes, -1 si la première chaîne est classée avant dans l'ordre alphabétique et 1 dans le cas contraire.

1
2
3
4
5

SELECT STRCMP('texte', 'texte') AS 'texte=texte', 
       STRCMP('texte','texte2') AS 'texte<texte2', 
       STRCMP('chaine','texte') AS 'chaine<texte', 
       STRCMP('texte', 'chaine') AS 'texte>chaine',
       STRCMP('texte3','texte24') AS 'texte3>texte24'; -- 3 est après 24 dans l'ordre alphabétique

Retrait et ajout de caractères

Répéter une chaîne

REPEAT(c, n) retourne le texte c, n fois.

1

SELECT REPEAT('Ok ', 3);

Compléter/réduire une chaîne

Les fonctions LPAD() et RPAD() appliquées à une chaîne de caractères retournent cette chaîne en lui donnant une longueur particulière, donnée en paramètre. Si la chaîne de départ est trop longue, elle sera raccourcie, si elle est trop courte, des caractères seront ajoutés, à gauche de la chaîne pour LPAD(), à droite pour RPAD(). Ces fonctions nécessitent trois paramètres : la chaîne à transformer (texte), la longueur désirée (long), et le caractère à ajouter si la chaîne est trop courte (caract).

1
2

LPAD(texte, long, caract)
RPAD(texte, long, caract)

1
2
3
4
5

SELECT LPAD('texte', 3, '@') AS '3_gauche_@', 
       LPAD('texte', 7, '$') AS '7_gauche_$', 
       RPAD('texte', 5, 'u') AS '5_droite_u', 
       RPAD('texte', 7, '*') AS '7_droite_*', 
       RPAD('texte', 3, '-') AS '3_droite_-';

Ôter les caractères inutiles

Il peut arriver que certaines de vos données aient des caractères inutiles ajoutés avant et/ou après le texte intéressant. Dans ce cas, il vous est possible d'utiliser la fonction TRIM(), qui va supprimer tous ces caractères. Cette fonction a une syntaxe un peu particulière que voici :

1

TRIM([[BOTH | LEADING | TRAILING] [caract] FROM] texte);

Je rappelle que ce qui est entre crochets est facultatif. On peut donc choisir entre trois options : BOTH, LEADING et TRAILING.

• BOTH : les caractères seront éliminés à l'avant, et à l'arrière du texte
• LEADING : seuls les caractères à l'avant de la chaîne seront supprimés
• TRAILING : seuls les caractères à l'arrière de la chaîne seront supprimés

Si aucune option n'est précisée, c'est BOTH qui est utilisé par défaut.

caract est la chaîne de caractères (ou le caractère unique) à éliminer en début et/ou fin de chaîne. Ce paramètre est facultatif : par défaut les espaces blancs seront supprimées. Et texte est bien sûr la chaîne de caractères à traiter.

1
2
3
4
5
6
7
8
9

SELECT TRIM('   Tralala  ') AS both_espace, 
       TRIM(LEADING FROM '   Tralala  ') AS lead_espace, 
       TRIM(TRAILING FROM '   Tralala  ') AS trail_espace,

       TRIM('e' FROM 'eeeBouHeee') AS both_e,
       TRIM(LEADING 'e' FROM 'eeeBouHeee') AS lead_e,
       TRIM(BOTH 'e' FROM 'eeeBouHeee') AS both_e,

       TRIM('123' FROM '1234ABCD4321') AS both_123;

Récupérer une sous-chaîne

La fonction SUBSTRING() retourne une partie d'une chaîne de caractères. Cette partie est définie par un ou deux paramètres : pos (obligatoire), qui donne la position de début de la sous-chaîne, et long (facultatif) qui donne la longueur de la sous-chaîne désirée (si ce paramètre n'est pas précisé, toute la fin de la chaîne est prise). Quatre syntaxes sont possibles :

• SUBSTRING(chaine, pos)
• SUBSTRING(chaine FROM pos)
• SUBSTRING(chaine, pos, long)
• SUBSTRING(chaine FROM pos FOR long)

1
2
3
4

SELECT SUBSTRING('texte', 2) AS from2,
        SUBSTRING('texte' FROM 3) AS from3,
        SUBSTRING('texte', 2, 3) AS from2long3, 
        SUBSTRING('texte' FROM 3 FOR 1) AS from3long1;

Recherche et remplacement

Rechercher une chaîne de caractères

INSTR(), LOCATE() et POSITION() retournent la position de la première occurrence d'une chaîne de caractères rech dans une chaîne de caractères chaine. Ces trois fonctions ont chacune une syntaxe particulière :

• INSTR(chaine, rech)
• LOCATE(rech, chaine) - les paramètres sont inversés par rapport à INSTR()
• POSITION(rech IN chaine)

Si la chaîne de caractères rech n'est pas trouvée dans chaine, ces fonctions retournent 0. Par conséquent, la première lettre d'une chaîne de caractères est à la position 1 (alors que dans beaucoup de langages de programmation, on commence toujours à la position 0).

LOCATE() peut aussi accepter un paramètre supplémentaire : pos, qui définit la position dans la chaîne à partir de laquelle il faut rechercher rech : LOCATE(rech, chaine, pos).

1
2
3
4

SELECT INSTR('tralala', 'la') AS fct_INSTR,
       POSITION('la' IN 'tralala') AS fct_POSITION,
       LOCATE('la', 'tralala') AS fct_LOCATE,
       LOCATE('la', 'tralala', 5) AS fct_LOCATE2;

Changer la casse des chaînes

Les fonctions LOWER(chaine) et LCASE(chaine) mettent toutes les lettres de chaine en minuscules, tandis que UPPER(chaine) et UCASE(chaine) mettent toutes les lettres en majuscules.

1
2
3
4

SELECT LOWER('AhAh') AS minuscule, 
        LCASE('AhAh') AS minuscule2, 
        UPPER('AhAh') AS majuscule,
        UCASE('AhAh') AS majuscule2;

Récupérer la partie gauche ou droite

LEFT(chaine, long) retourne les long premiers caractères de chaine en partant de la gauche, et RIGHT(chaine, long) fait la même chose en partant de la droite.

1

SELECT LEFT('123456789', 5), RIGHT('123456789', 5);

Inverser une chaîne

REVERSE(chaine) renvoie chaine en inversant les caractères.

1

SELECT REVERSE('abcde');

Remplacer une partie par autre chose

Deux fonctions permettent de remplacer une partie d'une chaîne de caractères : INSERT() et REPLACE().

• INSERT(chaine, pos, long, nouvCaract): le paramètre chaine est la chaîne de caractères dont on veut remplacer une partie, pos est la position du premier caractère à remplacer, long le nombre de caractères à remplacer, et nouvCaract est la chaîne de caractères qui viendra remplacer la portion de chaine choisie.
• REPLACE(chaine, ancCaract, nouvCaract) : tous les caractères (ou sous-chaînes) ancCaract seront remplacés par nouvCaract.

1
2
3

SELECT INSERT('texte', 3, 2, 'blabla') AS fct_INSERT, 
        REPLACE('texte', 'e', 'a') AS fct_REPLACE, 
        REPLACE('texte', 'ex', 'ou') AS fct_REPLACE2;

Concaténation

Concaténer deux chaînes de caractères signifie les mettre bout à bout pour n'en faire qu'une seule. Deux fonctions scalaires permettent la concaténation : CONCAT() et CONCAT_WS(). Ces deux fonctions permettent de concaténer autant de chaînes que vous voulez, il suffit de toutes les passer en paramètres. Par conséquent, ces deux fonctions n'ont pas un nombre de paramètres défini.

• CONCAT(chaine1, chaine2,…) : renvoie simplement une chaîne de caractères, résultat de la concaténation de toutes les chaînes passées en paramètres.
• CONCAT_WS(separateur, chaine1, chaine2) : même chose que CONCAT(), sauf que la première chaîne passée sera utilisée comme séparateur, donc placée entre chacune des autres chaînes passées en paramètres.

1

SELECT CONCAT('My', 'SQL', '!'), CONCAT_WS('-', 'My', 'SQL', '!');

FIELD(), une fonction bien utile pour le tri

La fonction FIELD(rech, chaine1, chaine2, chaine3,…) recherche le premier argument (rech) parmi les arguments suivants (chaine1, chaine2, chaine3,…) et retourne l'index auquel rech est trouvée (1 si rech = chaine1, 2 si rech = chaine2,…). Si rech n'est pas trouvée parmi les arguments, 0 est renvoyé.

1

SELECT FIELD('Bonjour', 'Bonjour !', 'Au revoir', 'Bonjour', 'Au revoir !') AS field_bonjour;

Par conséquent, FIELD peut être utilisée pour définir un ordre arbitraire dans une clause ORDER BY.

Exemple : ordonnons les espèces selon un ordre arbitraire. La fonction FIELD() dans la clause SELECT n'est là que pour illustrer la façon dont ce tri fonctionne.

1
2
3

SELECT nom_courant, nom_latin, FIELD(nom_courant, 'Rat brun', 'Chat', 'Tortue d''Hermann', 'Chien', 'Perroquet amazone') AS resultat_field
FROM Espece
ORDER BY FIELD(nom_courant, 'Rat brun', 'Chat', 'Tortue d''Hermann', 'Chien', 'Perroquet amazone');

Code ASCII

Les deux dernières fonctions que nous allons voir sont ASCII() et CHAR(), qui sont complémentaires. ASCII(chaine) renvoie le code ASCII du premier caractère de la chaîne passée en paramètre, tandis que CHAR(ascii1, ascii2,…) retourne les caractères correspondant aux codes ASCII passés en paramètres (autant de paramètres qu'on veut). Les arguments passés à CHAR() seront convertis en entiers par MySQL.

1

SELECT ASCII('T'), CHAR(84), CHAR('84', 84+32, 84.2);

Exemples d'application et exercices

Ce chapitre a été fort théorique jusqu'à maintenant. Donc pour changer un peu, et vous réveiller, je vous propose de passer à la pratique, en utilisant les données de notre base elevage. Ces quelques exercices sont faisables en utilisant uniquement les fonctions et opérateurs mathématiques que je vous ai décrits dans ce chapitre.

On commence par du facile

1. Afficher une phrase donnant le prix de l'espèce, pour chaque espèce

Par exemple, afficher "Un chat coûte 100 euros.", ou une autre phrase du genre, et ce pour les cinq espèces enregistrées.

1
2
3
4
5
6
7

SELECT CONCAT('Un(e) ', nom_courant, ' coûte ', prix, ' euros.') AS Solution
FROM Espece;

-- OU

SELECT CONCAT_WS(' ','Un(e)', nom_courant, 'coûte', prix, 'euros.') AS Solution
FROM Espece;

2. Afficher les chats dont la deuxième lettre du nom est un "a"

1
2
3
4
5

SELECT Animal.nom, Espece.nom_courant 
FROM Animal
INNER JOIN Espece ON Animal.espece_id = Espece.id
WHERE Espece.nom_courant = 'Chat'
AND SUBSTRING(nom, 2, 1) = 'a';

Puis on corse un peu

1. Afficher les noms des perroquets en remplaçant les "a" par "@" et les "e" par "3" pour en faire des perroquets Kikoolol

1
2
3
4

SELECT REPLACE(REPLACE(nom, 'a', '@'), 'e', '3') AS Solution
FROM Animal
INNER JOIN Espece ON Animal.espece_id = Espece.id
WHERE Espece.nom_courant LIKE 'Perroquet%';

2. Afficher les chiens dont le nom a un nombre pair de lettres

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

SELECT nom, nom_courant
FROM Animal
INNER JOIN Espece ON Animal.espece_id = Espece.id
WHERE nom_courant = 'Chien' 
AND CHAR_LENGTH(nom)%2 = 0;

-- OU

SELECT nom, nom_courant
FROM Animal
INNER JOIN Espece ON Animal.espece_id = Espece.id
WHERE nom_courant = 'Chien' 
AND CHAR_LENGTH(nom) MOD 2 = 0;

-- OU

SELECT nom, nom_courant
FROM Animal
INNER JOIN Espece ON Animal.espece_id = Espece.id
WHERE nom_courant = 'Chien' 
AND MOD(CHAR_LENGTH(nom),2) = 0;

En résumé

• Concaténer deux chaînes de caractères signifie les mettre bout à bout.
• Il ne faut pas hésiter, pour obtenir le résultat voulu, à combiner plusieurs fonctions ensemble.
• Pour savoir si un nombre est multiple d'un autre, on peut utiliser le modulo.

• Rappels et introduction
• Sommaire
• Fonctions d'agrégation