La fonction de sous-chaîne SQL en 5 exemples

Vous travaillez avec des données texte en SQL ? Nous vous expliquons comment obtenir des valeurs à partir de n'importe quel point d'une chaîne.

Lorsque vous pensez à travailler avec des données en SQL, votre première pensée est probablement une base de données remplie de chiffres et votre code SQL effectuant des calculs très sophistiqués. Mais le texte est aussi une donnée ! Il est très courant de trouver des données textuelles dans les bases de données. Non seulement vous devez les extraire, mais souvent vous devez aussi les manipuler. Les fonctions qui vous permettent de le faire sont appelées fonctions texte.

Pour tous ceux qui veulent s'exercer aux fonctions SQL, je recommande notre cours interactif Fonctions SQL standards cours interactif. Il contient 211 exercices et vous apprend à utiliser les fonctions textuelles, numériques et de date et heure courantes en SQL.

L'une des fonctions texte les plus courantes abordées dans le cours est SUBSTRING(). Dans cet article, nous vous proposons cinq exemples professionnels réels qui couvrent les principales utilisations de cette fonction. Certains exemples peuvent sembler compliqués si vous n'êtes pas familier avec les fonctions texte, alors assurez-vous d'avoir à portée de main l'antisèche Fonctions SQL standards ou un aperçu des fonctions texte SQL.

Qu'est-ce que la fonction SUBSTRING() ?

SUBSTRING() est une fonction texte qui vous permet d'extraire des caractères d'une chaîne de caractères. Sa syntaxe est la suivante

SUBSTRING(expression, start, length)

Pour l'argument expression, vous écrivez un littéral de chaîne ou spécifiez une colonne dont vous voulez extraire la sous-chaîne. L'argument start est un nombre entier indiquant la position numérique du caractère dans la chaîne où la sous-chaîne commence. L'argument length, comme son nom l'indique, définit la longueur, une valeur entière, de la sous-chaîne à renvoyer.

Comment fonctionne la fonction SUBSTRING() ?

L'indice se trouve dans le nom même de la fonction. Une sous-chaîne est une chaîne de caractères à l'intérieur de la chaîne principale. Par conséquent, SUBSTRING() extrait une sous-chaîne comme vous le spécifiez dans son argument.

Cela fonctionne comme suit :

Dans la chaîne ci-dessus, la sous-chaîne qui commence à la position 1 et qui a une longueur de trois caractères est ‘STR’.

Maintenant que nous avons abordé les principes, laissez-moi vous montrer plusieurs exemples. En commençant, bien sûr, par le plus simple !

Exemple 1 : Sous-chaîne de caractères à partir d'une chaîne littérale

La fonction SUBSTRING() renvoie une sous-chaîne à partir de la chaîne de caractères de votre choix. Vous pouvez écrire la chaîne explicitement comme argument, comme ceci :

SELECT SUBSTRING('This is the first substring example', 9, 10) AS substring_extraction;

Cela signifie que Je veux trouver une sous-chaîne du texte ‘This is the first substring example’. Les arguments indiquent que la sous-chaîne commence au 9e caractère de la chaîne et que sa longueur est de 10 caractères.

Voyons ce que ce code renvoie :

Il y a une colonne et une ligne. La sous-chaîne extraite est ‘the first’. Il s'agit de l'utilisation la plus basique de SUBSTRING(); le code n'utilise même pas de tableau !

La table Employés

Pour vous montrer des exemples plus intéressants, j'ai besoin de données. Laissez-moi vous présenter une table nommée employees.

Cette table stocke des informations sur les employés d'une entreprise imaginaire, Kooler, dans les colonnes suivantes :

• id - L'ID de l'employé.
• first_name - Le prénom de l'employé.
• last_name - Le nom de famille de l'employé.
• email - L'adresse électronique de l'employé.
• job_title - Titre du poste de l'employé.
• department - Le département de l'employé.
• start_date - La date d'entrée de l'employé chez Kooler.

Voici les premières lignes pour vous donner une idée des données :

Exemple 2 : sous-chaîne d'une colonne

Comme vous pouvez l'imaginer, l'écriture explicite de l'expression de chaîne n'est pas la seule façon d'utiliser SUBSTRING(). Vous pouvez également l'utiliser sur une colonne d'un tableau.

Voici un exemple de ce type. Je veux trouver les initiales de tous les employés. J'utilise la colonne email car je sais que les deux premières lettres de l'adresse électronique sont les initiales :

SELECT first_name,
	 last_name,
	 email,
	 SUBSTRING(email, 1, 2) AS employee_initials
FROM employees;

Je spécifie la colonne email dans la fonction. Obtenir les deux premières lettres de l'adresse électronique signifie que la sous-chaîne commence au premier caractère pour une longueur de deux caractères. On obtient ainsi le résultat souhaité :

Exemple 3 : Sous-chaîne sans l'argument de longueur

Vous pouvez omettre l'argument de longueur dans SUBSTRING(), et la fonction fonctionne toujours. Un bon exemple est celui où vous souhaitez afficher uniquement l'année de la date de début d'emploi. Vous voyez, la colonne start_date n'est pas très conviviale pour cela. Cette date est écrite sous forme de données texte au format MM/YYYY.

Heureusement, SUBSTRING() résout ce problème :

SELECT first_name,
	 last_name,
	 start_date,
	 SUBSTRING(start_date, 4) AS start_year
FROM employees;

Pour obtenir l'année à partir de la colonne start_date, il suffit de définir le début de la sous-chaîne. Dans ce code, la sous-chaîne commence au quatrième caractère. Comme j'ai omis l'argument de la longueur, la longueur de la sous-chaîne est égale à la distance qui sépare le quatrième caractère de la fin de la chaîne. C'est ainsi que j'obtiens facilement l'année, comme vous le voyez ci-dessous :

Exemple 4 : POSITION() et CHARINDEX()

Revenons à l'utilisation des e-mails. Selon la politique de l'entreprise, le point local d'une adresse électronique (c'est-à-dire la partie située avant le "@") est également le nom d'utilisateur de l'employé qui lui permet de se connecter à toutes les applications de l'entreprise. Vous devez extraire ce nom d'utilisateur. Voici comment procéder :

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

Les deux premiers arguments sont ceux que vous avez déjà vus. Je veux extraire une sous-chaîne de la colonne email, et je veux qu'elle commence au premier caractère de la chaîne. Mais maintenant, la longueur de la sous-chaîne est différente pour chaque employé. Comment puis-je indiquer à la fonction de renvoyer tous les caractères avant le signe "@" ?

J'utilise POSITION(), qui est équivalent à CHARINDEX() dans SQL Server ou MySQL. Elle localise le caractère spécifié dans la chaîne et renvoie sa position numérique. Ainsi, la longueur de la sous-chaîne correspondant au nom d'utilisateur de l'employé est égale à POSITION('@' IN email)-1. Pourquoi moins un ? Parce que je ne veux pas que '@' soit inclus dans le nom d'utilisateur de l'employé.

Voici le résultat :

Exemple 5 : LENGTH() + POSITION()

Le dernier exemple vous montre comment trouver le poste d'un employé à partir des données. En travaillant chez Kooler, je sais comment les titres des postes sont formés : d'abord l'ancienneté de l'employé, puis le département, puis le poste. Par exemple, "Assistant commercial junior" signifie que l'employé a une ancienneté junior, qu'il fait partie du service commercial et qu'il travaille comme assistant.

En utilisant SQL, je peux extraire cela comme une sous-chaîne :

SELECT first_name,
       last_name,
       job_title,
       SUBSTRING(job_title, LENGTH(job_title) - POSITION(' ' IN REVERSE(job_title))+2) AS position
FROM employees;

Voici un autre exemple d'omission de l'argument de la longueur, bien qu'un peu plus complexe. Comme toujours, je commence par spécifier la colonne de chaîne de caractères - job_title dans ce cas. Ensuite, je dois trouver une sous-chaîne composée uniquement du dernier mot de l'intitulé du poste.

Pour ce faire, j'utilise d'abord LENGTH(). Elle renvoie la longueur de la chaîne de caractères dans la colonne job_title. C'est un début ; c'est la longueur des trois mots ensemble, y compris les espaces vides. Si je pouvais en soustraire le nombre de caractères du dernier mot, j'obtiendrais la longueur des deux premiers mots, ce qui me donnerait le début de la sous-chaîne souhaitée.

C'est un peu compliqué car les noms des postes ont des longueurs différentes. La seule chose qui sépare les mots est l'espace blanc. Ainsi, pour obtenir la longueur du troisième mot de la chaîne, je dois compter le nombre de caractères jusqu'à l'espace blanc, mais... à partir de la droite.

La fonction POSITION() sauve à nouveau la mise, mais cette fois-ci en combinaison avec REVERSE(). La fonction REVERSE() inverse l'expression de la chaîne de caractères de sorte que "Junior Sales Assistant" devient "tnatsissA selaS roinuJ". Le dernier mot devient le premier ; le mot lui-même est également inversé, mais cela n'a pas d'importance ici.

POSITION() trouve la position de l'espace vide après le premier mot de la chaîne inversée. Ceci est égal à la position de l'espace blanc avant le dernier mot dans la chaîne originale (non inversée).

Ouf ! Maintenant, si je soustrais ce nombre de la longueur totale de la chaîne originale, j'obtiens le début de la sous-chaîne, n'est-ce pas ?

Eh bien, pas tout à fait ! En utilisant cette différence, on obtient une sous-chaîne qui inclut la dernière lettre du deuxième mot et l'espace blanc avant le dernier mot. Comment cela se fait-il ?

Deux choses. L'argument de départ de la fonction SUBSTRING() est inclusif. De plus, POSITION() calcule la position de l'espace blanc, et non le nombre de caractères jusqu'à l'espace blanc. Je dois donc ajouter 2 pour obtenir ce résultat :

Maintenant que j'ai présenté quelques autres fonctions, vous voudrez peut-être jeter un coup d'œil à d'autres fonctions de texte qui pourraient vous être utiles.

En savoir plus sur SUBSTRING () et le travail avec des données texte

Vous savez maintenant quand et comment utiliser SUBSTRING(). Il est temps de vous entraîner !

Il existe d'autres fonctions texte, et pas seulement SUBSTRING(). Vous pouvez les trouver (et bien plus encore !) dans le Fonctions SQL standards cours.