Une question fréquente que je vois de la part de nombreuses personnes novices en T-SQL est de savoir comment trouver des données dans une chaîne et les extraire. C’est une demande très courante, car nos bases de données contiennent de nombreuses chaînes de caractères. Nous constatons souvent que les personnes qui utilisent des applications intègrent des informations dans une chaîne, en espérant que le programme pourra facilement supprimer ces informations par la suite. Dans cet article, je vais voir comment extraire ces données à l’aide de SUBSTRING, CHARINDEX et PATINDEX.
C’est un article de retour aux bases qui, je l’espère, sera utile aux développeurs et aux DBA qui sont nouveaux sur SQL Server et qui cherchent à améliorer leurs compétences. N’hésitez pas à le faire circuler.
Trouver la commande cohérente
Un exemple est un numéro de facture ou un numéro de commande. J’ai souvent vu ces données intégrées dans des champs de texte, avec une exigence ultérieure d’extraire ce numéro du champ. C’est un type commun de données qui est ajouté à un champ dans une table quelque part, comme dans une table de clients. Nous pourrions avoir des utilisateurs, ou une application, qui décident d’ajouter ces données pour dénormaliser nos données.
Supposons que nous ayons une table qui contient des informations telles que celles-ci :
CREATE TABLE Customers( CustomerID INT, CustomerName VARCHAR(500), CustomerNotes VARCHAR(MAX), Active TINYINT);GOINSERT dbo.Customers ( CustomerID , CustomerName , CustomerNotes , Active ) VALUES ( 1, 'Acme Inc', 'Last PO:20154402', 1) , ( 2, 'Roadrunner Enterprises', 'Last PO:20140322', 1 ) , ( 3, 'Wile E. Coyote and Sons', 'Unreliable payments', 0)
Si je regarde les données, nous voyons que quelqu’un a décidé d’inclure des informations importantes dans le champ notes. Je suis sûr que de nombreuses personnes expérimentées vont grimacer devant cette utilisation des champs d’une table, mais cela arrive plus souvent que beaucoup d’entre nous ne le souhaiteraient.
Si je veux maintenant extraire le PO de ce champ, peut-être pour un rapport nécessaire, ou peut-être parce que je vais ETLer ces données à un endroit plus approprié, je peux utiliser la fonction SUBSTRING en T-SQL. J’utilise cette fonction lorsque je sais où, à l’intérieur d’une chaîne, je cherche à obtenir des données.
Dans ce cas, je peux voir que les 8 premiers caractères du champ CustomerNotes sont souvent « Last PO : ». Avec cela, je peux commencer au 9ème caractère et ensuite obtenir les 8 caractères suivants (longueur de la commande). Je vais utiliser cette requête.
SELECT CustomerID , 'PO' = SUBSTRING(CustomerNotes, 9, 8)FROM dbo.Customers
Cette requête va retourner les PO, mais j’obtiens d’autres données.
Pas d’inquiétude, je peux facilement filtrer cela (une discussion pour un autre article).
SELECT CustomerID , 'PO' = SUBSTRING(CustomerNotes, 9, 8)FROM dbo.Customers WHERE customerNotes LIKE '%PO%'
Maintenant, j’ai terminé, non ? Eh bien, peut-être pas.
Une commande incohérente
Dans les données que j’ai examinées jusqu’à présent, le numéro de commande est toujours au bon endroit. Cependant, supposons que nos saisisseurs ne travaillent pas tous de la même manière avec les clients. Voici un peu plus de données pour montrer ce que je veux dire:
INSERT dbo.Customers ( CustomerID , CustomerName , CustomerNotes , Active ) VALUES ( 4, 'Beep Beep Enterprises', 'Remember their slogan: We go fast. Last PO:20154402', 1) , ( 5, 'Goldberg Supplies', 'Preferred. Last PO:20140322', 1 ) , ( 6, 'Bugs Deliveries', 'Fast Last PO:20145554', 0)
Maintenant, exécutons notre script du dessus. Nous obtenons ces données:
Pas tout à fait ce que nous voulons. Le problème ici est que le début de la SUBSTRING n’est pas ce que nous voulons. Nous devons commencer par l’emplacement du numéro de commande, peut-être par l’emplacement de » PO : « . Comment pouvons-nous obtenir cela ?
Nous avons quelques choix, mais entrez CHARINDEX et PATINDEX. Les deux nous permettent de rechercher une chaîne et de trouver une autre chaîne à l’intérieur de celle-ci. L’un ou l’autre peut fonctionner ici, mais laissez-moi vous montrer comment ceux-ci fonctionnent sur nos données de test. Je vais exécuter cette requête:
SELECT CustomerID , CustomerNotes , 'Charindex_StartPO' = CHARINDEX('PO:', CustomerNotes) , 'Patindex_StartPO' = PATINDEX('%PO:%', CustomerNotes) , 'PO' = SUBSTRING(CustomerNotes, 9, 8) FROM dbo.Customers
Et obtenir ces résultats:
Notez que nous pouvons voir ici que les deux fonctions renvoient la même valeur, la position de départ du « P » dans « PO ». Il existe quelques différences. CHARINDEX peut commencer à une certaine position dans la chaîne, tandis que PATINDEX peut prendre des caractères génériques. Dans ce cas simpliste, nous pouvons utiliser l’un ou l’autre.
Je vais utiliser CHARINDEX ici, et modifier ma requête de la manière suivante :
SELECT CustomerID , 'PO' = SUBSTRING(CustomerNotes, CHARINDEX('PO:', CustomerNotes), 8) FROM dbo.Customers WHERE customerNotes LIKE '%PO%';
Cela me donne ceci, qui n’est pas ce que je veux.
J’ai oublié que CHARINDEX me donne la position de début du PO, je dois donc ajouter à cette valeur. Voici une requête qui fonctionne:
SELECT CustomerID , 'PO' = SUBSTRING(CustomerNotes, CHARINDEX('PO:', CustomerNotes)+3, 8) FROM dbo.Customers WHERE customerNotes LIKE '%PO%';
Notez que j’ai ajouté 3 au résultat de la fonction CHARINDEX. Voici les résultats:
La commande grandit
Il semble que ce soit une bonne requête, mais imaginons que nous ajoutons un peu plus de données.
Notez que dans ce cas, nous avons des commandes qui ont grandi. Certains ont 8 caractères, d’autres en ont 9. Certes, nous pouvons juste prendre 9 caractères, mais nous pourrions passer à 10 ou plus. De plus, nous avons d’autres notes après le bon de commande par endroits.
Modifions notre requête pour voir ce que nous pouvons faire. J’ai ajouté un twist à mon CHARINDEX.
SELECT CustomerID , CustomerNotes , 'Start of PO' = CHARINDEX('PO:', CustomerNotes)+3 , 'End of PO' = CHARINDEX(' ', CustomerNotes, CHARINDEX('PO:', CustomerNotes)+3) , 'PO' = SUBSTRING(CustomerNotes, CHARINDEX('PO:', CustomerNotes)+3, 8) FROM dbo.Customers WHERE customerNotes LIKE '%PO%';
Voici les résultats:
Si nous regardons de près, nous voyons que notre dernière entrée, avec du texte après le PO nous donne un résultat CHARINDEX. C’est parce que nous recherchons une chaîne de caractères, nous obtenons un 0 si aucune entrée n’est trouvée. Seul le client 8 a un espace après le PO. Cela signifie que nous pouvons calculer la longueur du PO pour la dernière entrée, mais qu’en est-il de toutes les autres entrées qui ont un format différent ?
Nous pouvons utiliser une instruction CASE ici, car nous avons deux possibilités ici. Un CASE vérifiera la présence d’un espace et renverra l’indice de l’espace à l’intérieur de la chaîne. L’autre renverra la longueur de la chaîne elle-même, lorsqu’il n’y a pas d’espace. Cela me donne un code comme celui-ci :
Mise à jour : mes calculs étaient incorrects. Changé de -3, à -2 dans le code ci-dessous.
SELECT CustomerID , CustomerNotes , 'Start of PO' = CHARINDEX('PO:', CustomerNotes)+3 , 'End of PO' = CASE WHEN CHARINDEX(' ', CustomerNotes, CHARINDEX('PO:', CustomerNotes)+3) = 0 THEN LEN(CustomerNotes) ELSE CHARINDEX(' ', CustomerNotes, CHARINDEX('PO:', CustomerNotes)+3) END , 'Real End of PO' = CASE WHEN CHARINDEX(' ', CustomerNotes, CHARINDEX('PO:', CustomerNotes)+3) = 0 THEN LEN(customernotes) ELSE CHARINDEX(' ', CustomerNotes, CHARINDEX('PO:', CustomerNotes)+3) END - CHARINDEX('PO:', CustomerNotes) , 'PO' = SUBSTRING(CustomerNotes , CHARINDEX('PO:', CustomerNotes)+3 , CASE WHEN CHARINDEX(' ', CustomerNotes, CHARINDEX('PO:', CustomerNotes)+3) = 0 THEN LEN(customernotes) ELSE CHARINDEX(' ', CustomerNotes, CHARINDEX('PO:', CustomerNotes)+3) END - CHARINDEX('PO:', CustomerNotes) - 2 ) FROM dbo.Customers WHERE customerNotes LIKE '%PO%';
Si nous regardons ce code, il est très similaire au code SUBSTRING que nous avons utilisé auparavant, mais maintenant au lieu d’une longueur fixe, 8, pour le nombre de caractères à retourner, nous retournons des valeurs avec une formule. La formule est essentiellement la fin réelle de l’OP (la 5ème colonne dans le jeu de résultats) et le début de l’OP. Il y a une déclaration CASE pour quand nous obtenons un zéro.
Maintenant si nous faisons le calcul, nous pouvons voir la longueur de chaque PO. Pour la plupart des PO, cela représente 8 caractères (11 caractères après le début du « P » dans « PO : »), mais 9 caractères pour le client 7 et 11 pour le client 8.
Certains d’entre vous pourraient s’interroger sur le -3 dans le code, mais si vous vous souvenez des règles de l’arithmétique, j’ai en fait reporté le moins sur la quantité représentant le début du numéro de la commande.
Conclusion
Ce n’est pas la fin des possibilités pour les commandes intégrées dans le champ a notes. Je pourrais avoir quelque chose comme « PO de test : 201530444. Nouveau test » et cela poserait des problèmes avec notre code. En fait, il y a plein d’autres cas que je devrais gérer dans le monde réel.
Cet article est issu de quelques problèmes d’extraction de chaînes que j’ai dû résoudre dans le monde réel, et ces types de problèmes se produisent. J’espère vous avoir donné quelques compétences à mettre en pratique qui vous aideront dans votre manipulation de chaînes de caractères SQL Server.
Comme pour toutes les techniques que vous pourriez apprendre ici, assurez-vous d’évaluer l’impact sur les performances. Exécutez votre code contre un grand ensemble de données de test et déterminez comment cette technique peut fonctionner par rapport à d’autres techniques. Je vous recommande d’utiliser une table de pointage pour générer des données à une échelle supérieure à celle de vos tables de production.
Les manipulations de chaînes peuvent être coûteuses en calcul dans SQL Server, alors assurez-vous de comprendre l’impact de vos choix avant de déployer du code sur un système de production.