Utilisation de LIKE avec des variables et des modèles dans SQL

Au fil des ans, j'ai constaté que certaines questions SQL revenaient régulièrement au sein de la Communauté des développeurs InterSystems, notamment concernant l'utilisation du prédicat LIKE dans différents contextes. Parmi les variantes courantes, on peut citer :

• l'utilisation de caractères spéciaux dans les requêtes
• l'utilisation de variables dans les requêtes

et bien d'autres dérivés. J'ai donc décidé d'écrire un article consacré au fonctionnement de LIKE dans InterSystems IRIS SQL, notamment lorsqu'il est utilisé avec des variables dans Embedded SQL, Dynamic SQL et les requêtes de classes, tout en abordant l'échappement de motifs et les recherches de caractères spéciaux.

Tout d'abord, je tiens à préciser qu'InterSystems IRIS SQL offre la plupart des fonctionnalités disponibles dans d'autres bases de données relationnelles implémentant une version ultérieure de la norme SQL. Il est toutefois important de préciser qu'outre l'accès relationnel, IRIS permet également d'utiliser d'autres modèles pour obtenir les mêmes données, par exemple des modèles objet ou document.

À ce propos, examinons le prédicat LIKE et son utilisation en SQL pour la recherche de motifs.

Informations de base

Commençons par quelques notions de base. Le prédicat LIKE est utilisé dans une clause WHERE (ou HAVING) d'une instruction SELECT, UPDATE ou DELETE pour filtrer les enregistrements selon que les valeurs d'une colonne correspondent à un motif spécifié. La syntaxe de base est la suivante :

SELECT column1, column2
  FROM table_name
 WHERE column_name LIKE pattern;

Le modèle peut inclure des caractères littéraux et deux caractères génériques principaux :

• % (signe de pourcentage) : représente zéro, un ou plusieurs caractères. Par exemple, « A% » correspond à toute chaîne commençant par « A », « %Z » à toute chaîne se terminant par « Z » et « %XY% » à toute chaîne contenant « XY ».
• _ (trait de soulignement) : représente un seul caractère. Par exemple, « A_C » correspond à « ABC », « AEC », etc., mais pas à « AC » ni à « ABBC ».

Par exemple, la requête suivante récupère tous les noms de la table Employés commençant par « Jo » :

SELECT Name
  FROM Employees
 WHERE Name LIKE 'Jo%';

Cette requête récupère tous les noms dont le deuxième caractère est « a » :

SELECT Name
  FROM Employees
 WHERE Name LIKE '_a%';

Il est essentiel de comprendre que LIKE effectue une correspondance de motif, et non une égalité. Bien que 'ABC' LIKE 'ABC' soit évalué comme vrai, il est généralement plus efficace d'utiliser l'opérateur = pour les correspondances exactes de chaînes ('ABC' = 'ABC'). LIKE est idéal pour les correspondances approximatives ou les recherches de sous-chaînes.

Inclure un caractère spécial dans une expression de recherche (clause d'échappement)

Je trouve la clause ESCAPE très pratique lorsque vous devez utiliser un caractère générique, par exemple %, dans votre instruction LIKE, et que vous souhaitez qu'il désigne un signe de pourcentage. Dans ce cas, vous pouvez utiliser la clause ESCAPE pour définir un caractère d'échappement. Tout caractère suivant immédiatement le caractère d'échappement dans le motif est traité comme un caractère littéral, et non comme un caractère générique. Par exemple, si vous disposez des données suivantes :

INSERT INTO Post.Promos(name, description) VALUES('Test 1', 'This is 40% discount')
INSERT INTO Post.Promos(name, description) VALUES('Test 2', 'This is 50% discount')
INSERT INTO Post.Promos(name, description) VALUES('Test 3', 'This is 10% discount')
INSERT INTO Post.Promos(name, description) VALUES('Test 4', 'some description')

et vous souhaitez trouver toutes les promotions dont la description contient l'expression « 50% discount », vous pouvez utiliser la requête suivante :

SELECT Name
  FROM Post.Promos
 WHERE Description LIKE '%50\% discount' ESCAPE '\'

Dans cet exemple, \ est défini comme caractère d'échappement. Le système traitera donc le signe % comme un caractère littéral et non un caractère générique, et recherchera une remise dont la description contient l'expression littérale « 50% discount ».

Recherche de caractères spéciaux

Si vous devez rechercher plusieurs caractères spéciaux ou tous les trouver dans une chaîne, vous ne pouvez pas utiliser le prédicat LIKE ; vous devez utiliser %MATCHES. Ce prédicat correspond à une valeur avec une chaîne de caractères contenant des caractères littéraux, des caractères génériques et des plages. Veuillez noter qu'un motif doit être spécifié au format logique, quel que soit le paramètre %SelectMode. Si vous souhaitez trouver toutes les valeurs contenant un caractère spécial, utilisez %MATCHES :

SELECT * 
  FROM Post.Promos p
 WHERE p.description %MATCHES '*[!”%#$&”()*+,-./:;<=>?@\%\_]*'

Cette fonction recherche toute description contenant au moins un des symboles listés. Vous pouvez utiliser la clause ESCAPE pour spécifier le caractère d'échappement, mais par défaut, il est défini sur « \" ; vous pouvez donc l'omettre, comme dans mon exemple.

La requête ci-dessus renvoie trois lignes avec des remises de 40 %, 50 % et 10 %, et non « some description ».

De plus, ce prédicat suit les règles générales de correspondance de motifs d'IRIS. Par exemple, pour un espace réservé, vous utiliserez « ? » et non « _ » :

SELECT * 
  FROM Post.Promos p
 WHERE p.name %MATCHES '???? [0-9]'

Cette fonction recherchera tous les noms composés de quatre caractères, d'un espace et d'un chiffre.

À propos des modèles, il existe également un prédicat %PATTERN qui permet de faire correspondre un modèle de codes de type de caractère et de littéraux aux valeurs de données. Pour effectuer la même recherche que ci-dessus, vous pouvez écrire la commande suivante :

SELECT * 
  FROM Post.Promos p
 WHERE p.name %PATTERN '1U.L1" "1N'

Correspondances :

1U — 1 lettre majuscule
.L — lettres minuscules
1" " — 1 espace
1N — 1 chiffre

Utilisation de variables avec LIKE

Voyons maintenant comment utiliser les variables dans vos requêtes. Il existe trois façons d'utiliser une instruction SQL dans votre code Object Script : Embedded SQL, Dynamic SQL et écriture d'une requête de classe.

Embedded SQL

Pour transmettre une valeur à une instruction Embedded SQL, vous devez utiliser un paramètre nommé (ou, en d'autres termes, des variables hôtes d'entrée et/ou de sortie), ce qui signifie qu'il doit avoir un nom. Par exemple, si nous souhaitons toujours trouver toutes les promotions avec une réduction de 50 %, nous écrirons la requête suivante :

 set param = "50\% discount"
 &sql(DECLARE C1 CURSOR FOR
       SELECT Name
         INTO :nameout
         FROM Post.Promos
        WHERE Description LIKE '%'_:param_'%' ESCAPE '\')
 &sql(OPEN C1)
       QUIT:(SQLCODE'=0)
 &sql(FETCH C1)
 WHILE (SQLCODE = 0) {
     WRITE nameout,!
    &sql(FETCH C1) }
  &sql(CLOSE C1)

Sa variable hôte d'entrée est param, et elle est égale à « 50% discount ». Pour que la requête comprenne que % fait partie du paramètre et non un espace réservé pour une longueur de caractères, j'utilise la clause ESCAPE.

De plus, veillez à bien placer les guillemets simples et doubles :

• En SQL, les premiers servent à marquer un champ dont le nom est réservé, par exemple « Groupe ». Les secondes permettent d'observer une chaîne.
• En ObjectScript, les premiers servent à observer la chaîne, tandis que les seconds n'ont aucun rapport avec les chaînes : c'est un opérateur unaire NOT.

Lorsque vous utilisez des paramètres, il n'est pas nécessaire d'insérer un guillemet simple entre les guillemets doubles.

set param = " '50\% discount ' "

pour indiquer au compilateur qu'il s'agit d'une chaîne. Dans ce cas, le moteur recherchera des guillemets simples dans la chaîne de recherche.

Dans l'exemple ci-dessus, la variable hôte de sortie est « nameout », où sera placée la valeur de la colonne Name. Elle pourra être utilisée ultérieurement dans le code.

SQL dynamique

Depuis la version 2015.2, Dynamic SQL peut accepter une valeur littérale en entrée dans une requête de deux manières :

• Paramètres d'entrée spécifiés à l'exécution à l'aide du caractère « ? »
• Variables hôtes d'entrée spécifiées lors de la préparation

La deuxième approche suit le même principe que pour Embedded SQL ci-dessus :

 set param = "50\% discount"
 set myquery = 3
 set tStatement = ##class(%SQL.Statement).%New()
 set myquery(1) = "SELECT Name"
 set myquery(2) = "FROM Post.Promos"
 set myquery(3) = "WHERE Description LIKE '%'_:param_'%' ESCAPE '\'"
 set qStatus = tStatement.%Prepare(.myquery)
 set tResult = tStatement.%Execute()
 while tResult.%Next() {
	 write tResult.Name, !
 }

à l'exception des variables hôtes de sortie, vous n'avez pas besoin de ces variables, car %SQL.StatementResult (type de résultat de tStatement.%Execute()) contiendra toutes les propriétés qui référencent les colonnes de l'instruction SELECT.

Dans la première approche, vous remplacez un paramètre par un point d'interrogation, puis, lors de l'appel de %Execute(), vous devez fournir les valeurs des paramètres dans le même ordre que le « ? » dans votre instruction :

 set param = "50\% discount"
 set myquery = 3
 set tStatement = ##class(%SQL.Statement).%New()
 set myquery(1) = "SELECT Name"
 set myquery(2) = "FROM Post.Promos"
 set myquery(3) = "WHERE Description LIKE '%'_?_'%' ESCAPE '\'"
 set qStatus = tStatement.%Prepare(.myquery)
 set tResult = tStatement.%Execute(param)
 while tResult.%Next() {
	 write tResult.Name, !
 }

Requête de classe

Les variables hôtes d'entrée sont utilisées dans les requêtes de classe selon les mêmes règles que dans le SQL embarqué et le Dynamic SQL :

Query GetDiscount(param As %String) As %SQLQuery [ SqlProc ]
{
SELECT Name FROM Post.Promos
 WHERE (Description LIKE '%'_:param_'%' ESCAPE '\')
}

Lors de l'appel de la requête, vous fournissez les paramètres dans l'ordre où ils sont écrits dans la signature de la méthode :

SELECT *
  FROM Post.Promos_GetDiscount('50\% discount')

---

J'espère que cet article répondra à certaines des questions fréquentes qui reviennent de temps à autre.

Si vous souhaitez lire un article plus approfondi sur les considérations de performance et les bonnes pratiques, ou si vous avez des commentaires, n'hésitez pas à les laisser dans la section commentaires ci-dessous.