Utiliser l’opérateur GLOB dans SQLite pour des recherches avec motifs

L’opérateur GLOB dans SQLite est un outil puissant pour effectuer des recherches de motifs dans vos données. Contrairement à l’opérateur LIKE, qui utilise les caractères spéciaux SQL standard pour les correspondances de motifs, GLOB utilise une syntaxe de style Unix pour les correspondances de motifs. Dans ce tutoriel, nous allons explorer l’utilisation de l’opérateur GLOB et ses différences avec l’opérateur LIKE.

Qu’est-ce que l’opérateur GLOB ?

L’opérateur GLOB est utilisé dans la clause WHERE des requêtes SQL pour rechercher des chaînes de caractères qui correspondent à un motif spécifique. Il est particulièrement utile lorsque vous avez besoin d’une recherche sensible à la casse ou lorsque vous préférez utiliser la syntaxe de motif de style Unix.

NOTE

Contrairement à l’opérateur LIKE, l’opérateur GLOB dans SQLite est sensible à la casse par défaut. Cela signifie que “A” et “a” sont considérés comme des caractères différents.

Syntaxe de l’opérateur GLOB

La syntaxe de base de l’opérateur GLOB est la suivante :

SELECT colonne1, colonne2, ...
FROM table
WHERE colonne GLOB 'motif';

Où motif est une chaîne de caractères qui peut contenir les caractères spéciaux suivants :

Caractère	Description
*	Correspond à zéro ou plusieurs caractères
?	Correspond à exactement un caractère
[abc]	Correspond à un seul caractère du jeu spécifié (a, b ou c)
[a-z]	Correspond à un seul caractère dans la plage spécifiée (de a à z)
[^abc]	Correspond à un seul caractère qui n’est pas dans le jeu spécifié

Différences entre GLOB et LIKE

Avant d’entrer dans les exemples d’utilisation de GLOB, il est important de comprendre les différences clés entre GLOB et LIKE :

1. Sensibilité à la casse : GLOB est sensible à la casse, tandis que LIKE ne l’est généralement pas (bien que cela puisse être modifié).
2. Caractères spéciaux : GLOB utilise des caractères spéciaux de style Unix (*, ?, [...]), tandis que LIKE utilise % et _.
3. Plages de caractères : GLOB prend en charge les plages de caractères ([a-z]), alors que LIKE ne le fait pas nativement.

Voici un tableau de comparaison des caractères spéciaux :

GLOB	LIKE	Description
*	%	Correspond à zéro ou plusieurs caractères
?	_	Correspond à exactement un caractère
[abc]	N/A	Correspond à un caractère du jeu
[^abc]	N/A	Correspond à un caractère qui n’est pas dans le jeu

Exemples d’utilisation de GLOB

Supposons que nous ayons une table produits avec les colonnes suivantes :

CREATE TABLE produits (
    id INTEGER PRIMARY KEY,
    nom TEXT NOT NULL,
    reference TEXT NOT NULL,
    description TEXT,
    prix REAL NOT NULL
);

INSERT INTO produits (nom, reference, description, prix) VALUES
('Smartphone XYZ', 'SM-X100', 'Smartphone haut de gamme', 899.99),
('Smartphone ABC', 'SM-A200', 'Smartphone milieu de gamme', 499.99),
('Tablette XYZ', 'TB-X300', 'Tablette 10 pouces', 349.99),
('Ordinateur portable ABC', 'LP-A400', 'Ordinateur portable 15 pouces', 1299.99),
('Écran 24 pouces', 'MN-2400', 'Écran HD 24 pouces', 199.99),
('Écran 27 pouces', 'MN-2700', 'Écran 4K 27 pouces', 349.99),
('Clavier sans fil', 'KB-W100', 'Clavier sans fil ergonomique', 79.99),
('Souris sans fil', 'MS-W200', 'Souris sans fil haute précision', 49.99);

Exemple 1 : Recherche simple avec *

Pour trouver tous les produits dont le nom commence par “Smartphone” :

SELECT id, nom, prix FROM produits
WHERE nom GLOB 'Smartphone*';

Résultat :

id | nom              | prix
---+------------------+-------
1  | Smartphone XYZ   | 899.99
2  | Smartphone ABC   | 499.99

Exemple 2 : Utilisation du caractère ?

Pour trouver tous les produits avec une référence qui se termine par un chiffre spécifique :

SELECT id, nom, reference FROM produits
WHERE reference GLOB '??-??0?';

Cette requête recherche les références qui ont exactement deux caractères, suivis d’un tiret, puis deux caractères, puis un “0”, puis un autre caractère.

Exemple 3 : Utilisation des ensembles de caractères […]

Pour trouver tous les produits dont le nom contient “XYZ” ou “ABC” :

SELECT id, nom FROM produits
WHERE nom GLOB '*[XA][YB][ZC]*';

Cette requête est plus complexe et utilisera des ensembles de caractères pour correspondre soit à “XYZ” soit à “ABC”.

Exemple 4 : Utilisation des plages de caractères [a-z]

Pour trouver tous les produits dont la référence commence par une lettre entre A et M, suivie de n’importe quelle lettre :

SELECT id, nom, reference FROM produits
WHERE reference GLOB '[A-M][A-Za-z]-*';

Exemple 5 : Utilisation des ensembles d’exclusion [^…]

Pour trouver tous les produits dont le nom ne commence pas par “S” ou “O” :

SELECT id, nom FROM produits
WHERE nom GLOB '[^SO]*';

Cette requête trouvera tous les produits dont le nom commence par une lettre qui n’est ni “S” ni “O”.

Cas d’utilisation pratiques de GLOB

1. Filtrage des fichiers dans une base de données

Si vous stockez des chemins de fichiers dans votre base de données, GLOB peut être très utile pour filtrer par extension ou par structure de répertoire :

-- Trouver tous les fichiers image
SELECT chemin FROM fichiers
WHERE chemin GLOB '*.jpg' OR chemin GLOB '*.png' OR chemin GLOB '*.gif';

-- Trouver tous les fichiers dans un répertoire spécifique
SELECT chemin FROM fichiers
WHERE chemin GLOB '/utilisateurs/jean/*';

2. Recherche de codes ou références avec un format spécifique

GLOB est particulièrement utile pour rechercher des codes ou des références qui suivent un format spécifique :

-- Trouver tous les produits avec une référence au format XX-1234
SELECT * FROM produits
WHERE reference GLOB '[A-Z][A-Z]-[0-9][0-9][0-9][0-9]';

3. Validation des données

Vous pouvez utiliser GLOB pour identifier les enregistrements qui ne respectent pas un format attendu :

-- Trouver tous les emails qui ne sont pas dans un format valide
SELECT id, email FROM utilisateurs
WHERE email NOT GLOB '*@*.*';

Bonnes pratiques et astuces

1. Sensibilité à la casse

Rappelez-vous que GLOB est sensible à la casse. Si vous avez besoin d’une recherche insensible à la casse avec la syntaxe de GLOB, vous pouvez convertir la colonne en minuscules ou en majuscules :

-- Recherche insensible à la casse avec GLOB
SELECT * FROM produits
WHERE LOWER(nom) GLOB '*smartphone*';

Cependant, cette approche peut être moins performante car elle empêche l’utilisation des index.

2. Performance

La recherche par motif avec GLOB peut être moins performante que les comparaisons directes, surtout sur de grandes tables. Considérez les points suivants :

• Utilisez des index sur les colonnes que vous recherchez fréquemment
• Essayez de rendre vos motifs aussi spécifiques que possible
• Évitez d’utiliser des motifs qui commencent par * si possible, car cela peut empêcher l’utilisation efficace des index

3. Combiner GLOB avec d’autres conditions

Vous pouvez combiner GLOB avec d’autres conditions dans votre clause WHERE pour affiner davantage vos résultats :

SELECT * FROM produits
WHERE nom GLOB 'Smartphone*' AND prix < 600;

4. Échapper les caractères spéciaux

Si vous devez rechercher littéralement un caractère spécial comme *, ? ou [, vous devez l’échapper. Dans SQLite, vous pouvez utiliser le caractère d’échappement \ :

-- Rechercher un produit avec un nom contenant un astérisque
SELECT * FROM produits
WHERE nom GLOB '*\**';

GLOB vs expressions régulières

SQLite prend également en charge les expressions régulières via l’extension REGEXP. Les expressions régulières offrent une syntaxe plus puissante et flexible que GLOB pour les correspondances de motifs complexes. Cependant, GLOB est généralement plus facile à utiliser pour les cas simples et fait partie de SQLite de manière native.

-- Exemple avec GLOB
SELECT * FROM produits WHERE nom GLOB 'Smart*';

-- Équivalent avec REGEXP (si l'extension est activée)
SELECT * FROM produits WHERE nom REGEXP '^Smart.*';