Un guide complet pour travailler avec les sous-chaînes en SQL

Dans cet article, nous allons voir comment travailler avec les sous-chaînes en SQL. Cela implique l'apprentissage de plusieurs approches, dont nous vous montrerons les tenants et les aboutissants.

Pour travailler avec les sous-chaînes en SQL, nous devons d'abord savoir ce qu'est une chaîne de caractères. En programmation, une chaîne est une donnée textuelle quelconque (un titre, une description, un nom). Une chaîne se compose d'un nombre et d'un type de caractères quelconques. Dans une base de données SQL, les chaînes sont généralement stockées sous forme de données de type CHAR ou VARCHAR.

Une sous-chaîne est une partie d'une chaîne. En d'autres termes, une sous-chaîne est une partie plus petite de la séquence de caractères. Si vous avez la chaîne "Cours SQL", "SQL" sera une sous-chaîne de cette chaîne. Nous avons illustré cela dans l'image ci-dessous :

Remarquez que l'image (et une chaîne) n'ignore pas les espaces vides ; les espaces vides sont également des caractères dans une chaîne.

Préparez-vous à travailler avec les sous-chaînes SQL

Il est indispensable de connaître la famille des fonctions de texte SQL lorsque l'on travaille avec des chaînes de caractères. Bien que les exemples de sous-chaînes soient aussi simples que possible, nous aurons besoin de fonctions de texte. L'utilisation de ces fonctions nécessite souvent la connaissance d'autres concepts SQL, tels que JOINs, le regroupement de données, le filtrage de données et l'utilisation de sous-requêtes. Vous pouvez pratiquer tous ces concepts dans notre La pratique du SQL piste. Il y a huit cours de pratique avec un total de plus de 950 défis de codage, donc vous aurez mal aux doigts à la fin de ce cours :) Mais c'est un peu le but, car la pratique est vraiment la seule façon de devenir fluide en SQL.

Nous allons utiliser la même approche ici et vous montrer autant d'exemples que cet article le permet. Pour extraire une sous-chaîne d'une chaîne de caractères en SQL, la fonction textuelle SUBSTRING(), au nom évocateur, semble être un choix évident. Mais ce n'est pas le seul choix possible ! Il en existe d'autres, comme l'opérateur LIKE et les fonctions de texte LEFT() et RIGHT().

Bien que cela semble facile, travailler avec des sous-chaînes SQL n'est pas toujours simple. Vous devez connaître les nuances de chaque fonction que vous souhaitez appliquer, savoir comment fonctionne l'indexation en SQL et comment SQL traite les données textuelles. De nombreux professionnels des données confirmeront que travailler avec des sous-chaînes en SQL peut devenir une expérience pénible et frustrante !

Nous allons faire en sorte que cela ne soit pas pénible pour vous. Commençons par le commencement.

Exemple de jeu de données

Nous allons travailler avec la table clients. Comme son nom l'indique, il s'agit d'une liste des clients de l'entreprise.

Vous pouvez créer la table en utilisant cette requête. Notez que la date de naissance est stockée dans un format de données textuel.

Commençons par extraire les sous-chaînes !

Vérifier si une chaîne contient une sous-chaîne en SQL

Une façon courante de vérifier si un texte contient une certaine sous-chaîne en SQL est d'utiliser l'opérateur LIKE ou ILIKE. Ils sont utilisés avec la clause WHERE pour filtrer les données. Les deux opérateurs trouveront des chaînes contenant une sous-chaîne semblable à celle définie dans leur modèle. La seule différence est que LIKE est sensible à la casse, alors que ILIKE ne l'est pas.

Utiliser LIKE et ILIKE pour vérifier si un texte contient une sous-chaîne

Votre collègue vous dit qu'un client a appelé pour vous transmettre un message. Comme il était pressé, il a oublié de noter le nom du client. Le client s'appelait Isaac, ou Dave, ou quelque chose comme ça.

Vous pouvez essayer de retrouver le client nommé Isaac ou Dave en écrivant cette requête :

SELECT *
FROM clients
WHERE first_name LIKE 'Dave' OR first_name LIKE 'Isaac';

Dans cette requête, vous sélectionnez toutes les colonnes de la table. Vous utilisez ensuite la clause WHERE et l'opérateur LIKE. Vous mettez les noms "Dave" et "Isaac" entre guillemets simples dans l'opérateur LIKE. La condition de la clause WHERE peut être lue comme suit : "Retourner tous les clients dont le prénom est Dave ou Isaac".

Exécutons le code et voyons... qu'il n'y a absolument rien ! Le code ne renvoie aucune ligne. Code stupide ! Si nous regardons le tableau, nous voyons qu'il y a des clients qui s'appellent Isaac Guardiola et Dave Trotter :

Alors pourquoi cela ne fonctionne-t-il pas ? La requête ci-dessus suppose que la sous-chaîne commence par "Dave" ou "Isaac". En examinant le tableau, nous pouvons voir que la chaîne commence en fait par leur prénom.

Pour que cette requête fonctionne, nous devons utiliser le caractère générique '%' avec LIKE. Ce caractère générique est utilisé pour rechercher une sous-chaîne au début, à la fin et n'importe où entre les deux. Tout dépend de l'endroit où vous positionnez le caractère générique. Voici un bref aperçu de son utilisation.

Réécrivons la requête :

SELECT *
FROM clients
WHERE first_name LIKE '%Dave%' OR first_name LIKE '%Isaac%';

Nous avons appris que les caractères génériques placés avant et après la sous-chaîne que nous voulons trouver signifient que la requête recherchera cette sous-chaîne n'importe où dans la chaîne. Cela nous permet d'ignorer la salutation devant chaque nom.

Voici le résultat :

Pour vous donner un autre exemple de l'utilisation de '%' avec LIKE, vous pouvez également rechercher tous les metteurs en scène de notre liste de clients.

Pour ce faire, vous placez le caractère générique après la sous-chaîne que vous recherchez, car cette sous-chaîne commence par "M." :

SELECT *
FROM clients
WHERE first_name LIKE 'Mr.%';

Voici le résultat.

Vous avez probablement remarqué que nous écrivons les sous-chaînes dans LIKE exactement comme elles sont écrites dans le tableau concernant les majuscules. C'est parce que l'opérateur LIKE est sensible à la casse.

Si vous voulez éviter cela ou si vous n'êtes pas sûr de la façon dont les données sont formatées, vous pouvez utiliser l'opérateur ILIKE. Comme il n'est pas sensible à la casse, vous pouvez écrire la sous-chaîne comme vous le souhaitez :

SELECT *
FROM clients
WHERE first_name ILIKE '%dave%' 
OR first_name ILIKE '%iSAaC%';

Le résultat est le même qu'avec LIKE:

Pour en savoir plus sur LIKE (et NOT LIKE), consultez cet article.

La fonction SUBSTRING()

SUBSTRING() est une fonction SQL utilisée pour extraire des sous-chaînes d'une chaîne. Elle vous permet de spécifier la chaîne de caractères dont vous voulez extraire la sous-chaîne. Vous définissez la sous-chaîne en spécifiant sa position de départ dans la chaîne et sa longueur.

Voici la syntaxe de la fonction :

SUBSTRING(string, start_position, length)

Comme toute fonction SQL, SUBSTRING() prend des arguments entre parenthèses. Deux arguments sont obligatoires :

• string - La chaîne de caractères à rechercher ; il peut s'agir d'une constante ou d'une colonne/expression.
• start_position - Une valeur entière définissant la position (le numéro ordinal du caractère) à laquelle la sous-chaîne commence, c'est-à-dire la position du premier caractère de la sous-chaîne.

Le troisième argument est length, qui est facultatif. Il définit la longueur d'une sous-chaîne comme le nombre de caractères qu'elle contiendra. Si cet argument est omis, la sous-chaîne sera le reste de la chaîne, en comptant à partir de la position de départ.

Voyons quelques exemples pour clarifier tout cela.

Utiliser SUBSTRING() pour extraire une sous-chaîne d'une chaîne constante

Une chaîne constante est une chaîne que vous écrivez entre guillemets simples comme premier argument dans SUBSTRING().

Par exemple, la chaîne constante dans le code suivant est 'La pratique du SQL Course', et nous voulons extraire 'Practice Course'.

SELECT SUBSTRING ('La pratique du SQL Course', 5) 
AS substring_extract

L'entier 5 est l'argument start_position. Cela signifie que la sous-chaîne commence à partir du cinquième caractère de la chaîne.

Pourquoi choisir 5 s'il n'y a que trois lettres ('SQL') devant notre sous-chaîne ? La sous-chaîne ne devrait-elle pas commencer à la quatrième position ? Attention : L'espace blanc compte également comme un caractère dans une chaîne !

Comme nous avons omis l'argument length, le code renvoie le reste de la chaîne en tant que sous-chaîne. En d'autres termes, le code renvoie ce résultat :

Utiliser SUBSTRING() pour extraire une sous-chaîne d'une colonne

Au lieu d'une valeur constante, vous pouvez également spécifier une colonne à partir de laquelle vous souhaitez extraire une sous-chaîne.

Affichons le nom et le prénom du client, mais sans la formule de politesse (M./Mme/Mx.) :

SELECT SUBSTRING (first_name, 5) AS first_name,
	 last_name
FROM clients;

Nous utilisons à nouveau la fonction SUBSTRING(). Le premier argument est le nom de la colonne, qui est first_name. Le deuxième argument est la position de départ de la sous-chaîne, qui est cinq. N'oubliez pas de compter les espaces vides ! Il y en a un après la salutation, de sorte que "M." comporte quatre caractères. Le cinquième caractère est la première lettre du nom du client.

Nous avons omis la longueur de la sous-chaîne et la deuxième colonne sélectionnée est le nom de famille du client, de sorte que le code renvoie ceci :

Utiliser SUBSTRING() pour extraire une sous-chaîne d'une longueur définie

Nous allons maintenant cesser d'éviter le troisième argument SUBSTRING(). Dans cet exemple, nous allons montrer comment extraire le jour et le mois de naissance des clients :

SELECT first_name,
	 last_name,
	 SUBSTRING (date_of_birth, 1, 6) AS birthday
FROM clients;

Après avoir sélectionné les noms et prénoms des clients, nous utilisons la fonction SUBSTRING(). Nous utiliserons la colonne date_of_birth pour extraire la date de naissance du client (mois et jour).

Après avoir passé la colonne en argument, nous spécifions la position de départ. Il s'agit du numéro 1, ce qui signifie que la sous-chaîne sera extraite à partir du premier caractère de la chaîne. Le chiffre 6 signifie que la sous-chaîne sera composée de six caractères : deux caractères pour le jour, un point, deux caractères pour le mois et un autre point.

Regardez le résultat :

Tous les clients sont là, avec leurs anniversaires extraits comme nous le souhaitions. Cette fois-ci, nous n'avons pas de problème pour afficher la salutation.

Utilisation de SUBSTRING() avec d'autres fonctions pour trouver un index de sous-chaîne

Il existe des fonctions qui peuvent être utilisées pour trouver un index dans la chaîne de caractères. Dans MySQL et PostgreSQL, cette fonction s'appelle POSITION(); dans SQL Server, elle s'appelle CHARINDEX(). Vous pouvez spécifier la sous-chaîne que vous recherchez et ces fonctions renverront sa position dans la chaîne.

Ces fonctions sont extrêmement utiles en combinaison avec SUBSTRING(). Examinons d'abord POSITION(), puis nous verrons un exemple avec CHARINDEX().

La fonction POSITION() vous permet de spécifier la sous-chaîne et de trouver sa position de départ. Utilisons-la pour extraire le nom d'utilisateur de chaque client à partir de son adresse électronique :

SELECT first_name,
	 last_name,
	 email,
	 POSITION('@' IN email) AS at_position,
	 SUBSTRING(email, 1, POSITION('@' IN email)-1) AS username
FROM clients;

Ci-dessus, nous utilisons POSITION() pour trouver le "@" dans l'adresse électronique de chaque utilisateur. Vous en avez besoin car tout ce qui précède ce caractère est le nom de l'utilisateur. La syntaxe de POSITION() est simple : il suffit de spécifier le caractère de recherche entre guillemets simples, puis de le faire suivre du mot-clé IN et du nom des colonnes dans lesquelles vous voulez chercher.

Cette colonne ne renvoie que la position du caractère. Nous devons l'intégrer dans SUBSTRING() pour obtenir le nom d'utilisateur. Les deux premiers arguments de SUBSTRING() sont familiers : nous regardons la colonne email, et nous voulons que notre sous-chaîne (un nom d'utilisateur) commence par le premier caractère de la chaîne.

Le troisième argument de SUBSTRING() est la longueur de la sous-chaîne. La longueur du nom d'utilisateur est le nombre de caractères précédant et excluant '@'. En d'autres termes, la longueur du nom d'utilisateur est la position de '@' moins un. Pourquoi moins ? Parce que sinon, '@' ferait partie du nom d'utilisateur.

Nous pouvons vérifier le résultat. Dans le 'iguardiola@gmail.com', le symbole '@' se trouve à la position 11, comme le montre la colonne at_position. La colonne nom d'utilisateur montre la sous-chaîne avant '@', qui est en effet 'iguardiola' pour ce client. Vous pouvez vérifier le reste des résultats de la même manière.

Le même code dans SQL Server devrait utiliser la fonction CHARINDEX():

SELECT first_name,
	 last_name,
	 email,
	 CHARINDEX('@', email) AS at_position,
	 SUBSTRING(email, 1, CHARINDEX('@', email)-1) AS username
FROM clients;

La logique est similaire à celle de la fonction POSITION(): vous spécifiez la sous-chaîne que vous recherchez, puis la colonne, les arguments étant séparés par une virgule.

Le code produit est le même.

Nous en avons maintenant terminé avec SUBSTRING(). Mais si vous avez besoin de plus de pratique, voici cinq autres exemples de SUBSTRING().

LEFT() et RIGHT() en SQL

Une autre façon d'extraire une sous-chaîne est d'utiliser les fonctions LEFT() ou RIGHT().

LEFT() La fonction LEFT() extrait la sous-chaîne à partir de la gauche, ce qui permet de définir la longueur de la sous-chaîne. La fonction RIGHT() fait la même chose mais du côté droit.

Utiliser LEFT() et RIGHT() pour extraire une sous-chaîne

Utilisons ces deux fonctions pour extraire quelques sous-chaînes : les trois premières lettres d'un nom de famille et l'année de naissance.

Voici le code.

SELECT first_name,
	 last_name,
	 LEFT(last_name, 3) AS last_name_substring,
	 RIGHT(date_of_birth, 4) AS year_of_birth
FROM clients;

Nous utilisons la fonction LEFT() pour obtenir les trois premières lettres du nom de famille. Elle part de la gauche et prend le nombre de caractères spécifié pour créer une sous-chaîne. La syntaxe est simple : nous spécifions le nom de la colonne, puis la longueur de la sous-chaîne.

RIGHT() Pour trouver l'année de naissance, on part de la droite et on prend le nombre de caractères spécifié pour créer une sous-chaîne. Ici, le premier argument est la colonne date_of_birth. Ensuite, nous comptons le nombre de caractères que nous voulons à partir de la droite, c'est-à-dire quatre, ce qui renverra l'année de naissance.

Comme LEFT() et RIGHT() sont des images inversées l'une de l'autre, nous aurions pu facilement utiliser l'une ou l'autre de ces fonctions pour trouver les deux sous-chaînes requises.

Voici le résultat de la requête ci-dessus :

Elle indique les trois premières lettres du nom de famille et l'année de naissance.

Devenez celui qui tire toutes les (sous-)chaînes de caractères en SQL

En tant qu'analyste de données, on attend de vous que vous tiriez toutes les (sous-)ficelles par vous-même. L'une des astuces pour y parvenir est de connaître les différentes façons de travailler avec les sous-chaînes, comme celles que nous vous avons montrées ici.

Connaître les différentes fonctions de manipulation des sous-chaînes vous permet de choisir celle qui convient le mieux à votre problème. Non seulement vous obtiendrez le résultat que vous souhaitez, mais vous obtiendrez également un code plus efficace et moins de lignes.

Pour atteindre ce niveau, vous devez travailler souvent avec des chaînes de caractères et utiliser quotidiennement toutes les fonctions mentionnées. En d'autres termes, vous devez écrire du code. Si vous avez besoin d'un environnement sûr pour le faire, notre cours La pratique du SQL cours vous offre exactement cela.

Bonne chance pour devenir le maître des sous-chaînes SQL !