Passer au contenu
SQLite

Fonctions Scalaires intégrées dans SQLite

Les fonctions scalaires dans SQLite sont des fonctions intégrées qui prennent un ou plusieurs arguments et renvoient une seule valeur. Elles permettent de manipuler et transformer des données directement dans vos requêtes SQL, ce qui peut simplifier grandement votre code et améliorer les performances en évitant des allers-retours entre la base de données et votre application.

Dans ce tutoriel, nous explorerons les fonctions scalaires les plus utiles de SQLite, avec des exemples concrets pour chacune.

Fonctions de manipulation de texte

Fonctions UPPER, LOWER et LENGTH

-- Création d'une table exemple
CREATE TABLE utilisateurs (
  id INTEGER PRIMARY KEY,
  nom TEXT,
  email TEXT
);


-- Insertion de quelques données
INSERT INTO utilisateurs (nom, email) VALUES
  ('Jean Dupont', '[email protected]'),
  ('Marie Martin', '[email protected]'),
  ('Thomas Bernard', '[email protected]');


-- Utilisation de UPPER() pour convertir en majuscules
SELECT nom, UPPER(nom) AS nom_majuscules FROM utilisateurs;


-- Utilisation de LOWER() pour normaliser les emails
SELECT email, LOWER(email) AS email_normalisé FROM utilisateurs;


-- Utilisation de LENGTH() pour compter les caractères
SELECT nom, LENGTH(nom) AS longueur_nom FROM utilisateurs;

Dans cet exemple :

  • UPPER() convertit une chaîne en majuscules, utile pour l’affichage ou les comparaisons insensibles à la casse
  • LOWER() convertit une chaîne en minuscules, souvent utilisé pour normaliser les données comme les emails
  • LENGTH() compte le nombre de caractères, ce qui peut être utile pour valider ou filtrer des données

Fonctions SUBSTR, INSTR et REPLACE

-- Extraction d'une partie d'une chaîne avec SUBSTR
SELECT email,
       SUBSTR(email, 1, INSTR(email, '@') - 1) AS nom_utilisateur,
       SUBSTR(email, INSTR(email, '@') + 1) AS domaine
FROM utilisateurs;


-- Recherche de position avec INSTR
SELECT nom,
       INSTR(nom, ' ') AS position_espace,
       SUBSTR(nom, 1, INSTR(nom, ' ') - 1) AS prénom
FROM utilisateurs;


-- Remplacement de texte avec REPLACE
SELECT email,
       REPLACE(email, '@example.com', '@nouvelle-entreprise.com') AS nouvel_email
FROM utilisateurs;

Dans ces exemples :

  • SUBSTR(X, Y, Z) extrait une sous-chaîne à partir de la position Y sur Z caractères
  • INSTR(X, Y) trouve la position de Y dans X, très utile pour localiser des caractères spécifiques
  • REPLACE(X, Y, Z) remplace toutes les occurrences de Y par Z dans X

Fonctions de manipulation de nombres

-- Création d'une table de produits
CREATE TABLE produits (
  id INTEGER PRIMARY KEY,
  nom TEXT,
  prix REAL,
  stock INTEGER
);


-- Insertion de quelques données
INSERT INTO produits (nom, prix, stock) VALUES
  ('Smartphone', 499.99, 25),
  ('Casque audio', 89.95, 50),
  ('Chargeur', 19.50, 100),
  ('Étui de protection', 24.75, 75);


-- Arrondissement avec ROUND
SELECT nom,
       prix,
       ROUND(prix) AS prix_arrondi,
       ROUND(prix, 1) AS prix_arrondi_1_décimale
FROM produits;


-- Valeur absolue avec ABS
SELECT stock,
       ABS(stock - 50) AS écart_par_rapport_à_50
FROM produits;


-- Fonction MAX et MIN sur des valeurs
SELECT nom, prix, stock,
       MAX(prix, 100) AS prix_minimum_100,
       MIN(stock, 80) AS stock_maximum_80
FROM produits;

Dans ces exemples :

  • ROUND(X, Y) arrondit X à Y décimales (Y est optionnel, 0 par défaut)
  • ABS(X) retourne la valeur absolue de X
  • MAX(X, Y, ...) et MIN(X, Y, ...) retournent respectivement le maximum et le minimum des valeurs fournies

Fonctions conditionnelles

-- Utilisation de COALESCE pour gérer les valeurs NULL
ALTER TABLE produits ADD COLUMN promotion REAL;
UPDATE produits SET promotion = 0.2 WHERE nom = 'Smartphone';
UPDATE produits SET promotion = 0.1 WHERE nom = 'Casque audio';


SELECT nom,
       prix,
       promotion,
       COALESCE(promotion, 0) AS promotion_par_défaut,
       prix * (1 - COALESCE(promotion, 0)) AS prix_final
FROM produits;


-- Utilisation de IFNULL
SELECT nom,
       prix,
       promotion,
       IFNULL(promotion, 0) AS promotion_ou_zéro
FROM produits;


-- Utilisation de IIF (ou IF) pour les conditions
SELECT nom,
       stock,
       IIF(stock > 50, 'Stock suffisant', 'Stock à surveiller') AS état_stock
FROM produits;


-- CASE pour les conditions plus complexes (bien que ce ne soit pas une fonction)
SELECT nom,
       prix,
       CASE
         WHEN prix < 20 THEN 'Bas prix'
         WHEN prix BETWEEN 20 AND 100 THEN 'Prix moyen'
         ELSE 'Prix élevé'
       END AS catégorie_prix
FROM produits;

Dans ces exemples :

  • COALESCE(X, Y, ...) retourne le premier argument non NULL
  • IFNULL(X, Y) retourne Y si X est NULL, sinon X (équivalent à COALESCE avec 2 arguments)
  • IIF(condition, valeur_si_vrai, valeur_si_faux) est une forme simplifiée de CASE

Fonctions de date et heure

-- Obtenir la date et l'heure actuelles
SELECT
    DATE('now') AS date_actuelle,
    TIME('now') AS heure_actuelle,
    DATETIME('now') AS date_heure_actuelle;


-- Manipulation de dates
SELECT
    DATE('now') AS aujourd_hui,
    DATE('now', '+1 day') AS demain,
    DATE('now', '+1 month') AS mois_prochain,
    DATE('now', '+1 year') AS année_prochaine;


-- Extraire des parties d'une date
CREATE TABLE commandes (
  id INTEGER PRIMARY KEY,
  client_id INTEGER,
  date_commande TEXT,
  montant REAL
);


INSERT INTO commandes (client_id, date_commande, montant) VALUES
  (1, '2023-01-15 14:30:00', 120.50),
  (2, '2023-02-20 09:15:00', 75.25),
  (1, '2023-03-10 16:45:00', 200.00),
  (3, '2023-03-15 11:30:00', 50.75);


-- Extraire l'année, le mois et le jour
SELECT
    date_commande,
    SUBSTR(date_commande, 1, 4) AS année,
    SUBSTR(date_commande, 6, 2) AS mois,
    SUBSTR(date_commande, 9, 2) AS jour
FROM commandes;

SQLite ne possède pas autant de fonctions de date que d’autres SGBD, mais il propose les fonctions essentielles pour manipuler les dates et les heures.

Autres fonctions utiles

-- Fonction RANDOM
SELECT RANDOM() AS nombre_aléatoire,
       ABS(RANDOM()) % 100 AS nombre_entre_0_et_99;


-- Fonction TYPEOF pour connaître le type de données
SELECT
    TYPEOF(42) AS type_entier,
    TYPEOF(3.14) AS type_réel,
    TYPEOF('Hello') AS type_texte,
    TYPEOF(NULL) AS type_null,
    TYPEOF(X'ABCD') AS type_blob;


-- Fonction QUOTE pour échapper des chaînes
SELECT QUOTE('Ceci est une "citation"') AS chaîne_échappée;

Résumé des principales fonctions scalaires

FonctionParamètresDescriptionCas d’usage
ABS(X)X: nombreRetourne la valeur absolue de XCalculer des écarts, distances
COALESCE(X,Y,...)X,Y,…: valeursRetourne la première valeur non NULLDéfinir des valeurs par défaut
IFNULL(X,Y)X: valeur, Y: alternativeRetourne Y si X est NULL, sinon XGérer les valeurs manquantes
IIF(B,X,Y)B: condition, X: si vrai, Y: si fauxRetourne X si B est vrai, sinon YLogique conditionnelle simple
INSTR(X,Y)X: chaîne, Y: sous-chaînePosition de Y dans X (1 = premier caractère)Rechercher dans des chaînes
LENGTH(X)X: chaîneNombre de caractères dans XValider la longueur des entrées
LOWER(X)X: chaîneConvertit X en minusculesNormaliser des chaînes pour comparaison
UPPER(X)X: chaîneConvertit X en majusculesMettre en forme pour affichage
MAX(X,Y,...)X,Y,…: valeursRetourne la plus grande valeurImposer une valeur minimale
MIN(X,Y,...)X,Y,…: valeursRetourne la plus petite valeurImposer une valeur maximale
RANDOM()AucunEntier aléatoireGénérer des données de test
REPLACE(X,Y,Z)X: chaîne, Y: à remplacer, Z: remplacementRemplace Y par Z dans XFormatage et nettoyage de données
ROUND(X,Y)X: nombre, Y: décimalesArrondit X à Y décimalesFormatage numérique
SUBSTR(X,Y,Z)X: chaîne, Y: début, Z: longueurExtrait une sous-chaîneExtraire parties de chaînes
TRIM(X,Y)X: chaîne, Y: caractères à enleverSupprime Y des extrémités de XNettoyage de données
TYPEOF(X)X: valeurRetourne le type de XDébogage et vérification de types