SQLShack
Les développeurs de bases de données ont souvent besoin de convertir une valeur séparée par des virgules ou d’autres éléments délimités en un format tabulaire. Les délimiteurs incluent le pipe « | », le dièse « # », le dollar « $ » et d’autres caractères. Le niveau de compatibilité 130 de SQL Server, et les versions suivantes, prennent en charge une fonction string_split() pour convertir les valeurs séparées par des délimiteurs en lignes (format tableau). Pour les niveaux de compatibilité inférieurs à 130, les développeurs le faisaient auparavant avec une fonction définie par l’utilisateur, qui intègre une boucle While ou un curseur pour extraire les données.
Pourquoi la saisie séparée par des virgules est-elle nécessaire pour une procédure ou une requête T-SQL ?
Exécuter un programme dans une itération en utilisant une boucle est une méthode courante dans l’application back-end ainsi que dans la base de données. Lorsque le nombre d’itérations est plus petit que prévu, le temps d’exécution peut être moindre cependant le nombre d’itérations est plus grand ce qui entraîne un temps de traitement plus long. Par exemple, si l’application extrait des données d’une base de données en exécutant un morceau de programme en boucle en analysant différents paramètres d’entrée, l’application devra envelopper la réponse de la base de données à chaque itération. Pour gérer ce type de situation, nous pouvons exécuter le même programme avec une seule exécution avec le parsing des valeurs séparées par des virgules ou des délimiteurs comme paramètre d’entrée dans la base de données et le rendre au format tabulaire dans le programme T-SQL pour procéder à la suite de la logique.
Aujourd’hui, de nombreuses plateformes ont été changées pour une architecture de microservices. Non seulement l’application, mais la conception de la base de données pourrait également être structurée dans une architecture basée sur les microservices et la plupart de la communication interne entre les bases de données est faite avec des références entières seulement. C’est la raison pour laquelle il faut obtenir un moyen rusé pour obtenir une meilleure exécution du côté de la base de données.
Les entrées multiples au même paramètre peuvent être accomplies avec des valeurs séparées par des virgules dans le paramètre d’entrée de la procédure stockée ou l’entrée de la fonction tabulaire, et utilisées avec la table dans une déclaration T-SQL. Il peut y avoir plus de situations pour cette stratégie lors de l’utilisation de T-SQL. Lors de la programmation avec SQL Server, un développeur doit diviser une chaîne de caractères en éléments à l’aide d’un séparateur. L’utilisation de la fonction split contre la chaîne de caractères définit le séparateur comme un paramètre d’entrée. La chaîne entière sera divisée et renvoyée sous forme de tableau. Dans certaines circonstances, le paramètre d’entrée peut être une combinaison de deux ou plusieurs valeurs séparées par des caractères pour chaque ensemble d’entrée. Par exemple :
N’203616, 198667, 193718, 188769,…’ N’1021|203616$1021|198667$1022|193718$1022|188769$…’ N’1021|203616,198667$1022|193718,188769$…’
L’exécution en single-thread avec des valeurs séparées par des virgules présente des avantages par rapport à l’exécution en multi-thread dans SQL Server :
- Exécution unique pour une partie du programme, ce qui se traduit par le nombre d’entrées
- Une seule opération de lecture ou d’écriture sur les tables de la base de données au lieu de plusieurs itérations en utilisant une boucle
- Utilisation unique des actifs du serveur
- Plus d’obstacles sur l’application back-end pour configurer une réponse à partir de plusieurs ensembles de résultats
- Un one-shot commit signifie que la base de données/Serveur SQL ne bloquera pas la transaction et ne ralentira pas les choses. Ainsi, aucune possibilité de blocage
- Facile de superviser la cohérence de la transaction
Fonction de fractionnement à l’aide d’une boucle
Dans la plupart des cas, un développeur écrit la fonction de valeur de tableau avec une boucle pour extraire les données des valeurs séparées par des virgules vers un format de liste ou de tableau. Une boucle extrait le tuple un par un avec la récursion. Des problèmes de performance peuvent survenir lorsque vous travaillez avec un paramètre d’entrée important, car son extraction prendra plus de temps. Ici, nous avons une fonction simple appelée split_string, qui utilise une boucle à l’intérieur d’une fonction de type tableau. Cette fonction permet deux arguments ou paramètres d’entrée (1 – chaîne d’entrée et 2 – délimiteur) :
|
…
1
2
3
4
5
6
7
8
9
.. 10
11
12
13
14
15
16
17
|
CREATE FUNCTION split_string
(
@in_string VARCHAR(MAX),
@delimètre VARCHAR(1)
)
Retourne @list TABLE(tuple VARCHAR(100)).
AS
BEGIN
WHILE LEN(@in_string) > 0
BEGIN
INSERT INTO @list(tuple)
SELECT left(@in_string, charindex(@delimiter, @in_string+’,’) -1) as tuple
SET @in_string = stuff(@in_string, 1, charindex(@delimiter, @in_string + @delimiter), »)
fin
RETOUR
END
|
Un tableau de jeu de résultats retourné par cette fonction contient chaque sous-chaîne du paramètre d’entrée chaîne qui est séparée par le délimiteur. Les sous-chaînes dans le tableau sont dans l’ordre dans lequel elles se produisent dans le paramètre d’entrée. Si le séparateur ou le délimiteur ne correspond à aucun élément de la chaîne d’entrée, le tableau de sortie ne comportera qu’un seul composant. La fonction tabulaire renverra l’ensemble des résultats dans un format ligne-colonne à partir de la chaîne séparée par des virgules.
|
1
|
SELECT * FROM split_string(‘1001,1002,1003,1004’, ‘,’)
|
Les versions récentes de SQL Server fournissent une fonction intégrée string_split() pour effectuer la même tâche avec pour paramètres d’entrée la chaîne de caractères et le délimiteur. Mais il existe un autre moyen efficace d’effectuer cette tâche en utilisant XML.
Fonction Split utilisant XML
La fonction Split utilisant la logique XML est une méthode utile pour séparer les valeurs d’une chaîne délimitée. L’utilisation de la logique XML avec l’approche XQUERY basée sur T-SQL permet de ne pas caractériser le T-SQL comme une récursion utilisant une boucle ou un curseur. Essentiellement, la chaîne de paramètres d’entrée est convertie en XML en remplaçant le nœud XML dans l’articulation T-SQL. Le XML résultant est utilisé dans XQUERY pour extraire la valeur du nœud dans le format de la table. Il en résulte également de meilleures performances.
Par exemple, nous avons une valeur séparée par des virgules avec la variable @user_ids dans l’articulation T-SQL ci-dessous. Dans une variable, la virgule est remplacée par les balises XML pour en faire un document de type données XML, puis extraite à l’aide de XQUERY avec le XPATH comme ‘/root/U’ :
|
1
. 2
3
4
. 5
6
7
8
9
10
|
DECLARE @user_ids NVARCHAR(MAX) = N’203616, 198667, 193718, 188769, 183820, 178871, 173922, 168973, 164024, 159075, 154126, 149177, 144228, 139279, 134330, 129381, 124432, 119483, 114534, 109585, 104636, 99687, 94738, 89789, 84840, 79891, 74942, 69993, 65044, 60095, 55146′
DECLARE @sql_xml XML = Cast(‘<root><U>’+ Replace(@user_ids, ‘,’, </U><U></U></root>’ AS XML)
SELECT f.x.value(‘.’, ‘BIGINT’) AS user_id
INTO #users
FROM @sql_xml.nodes(‘/root/U’) f(x)
SELECT *
FROM #users
|
Dans la fonction ci-dessus, nous avons utilisé le type de données VARCHAR (100) pour la valeur extraite plutôt que BIGINT. Il peut y avoir des changements dans la chaîne de paramètres d’entrée, aussi. Par exemple, si la chaîne d’entrée comporte un ‘,’ supplémentaire au début ou à la fin de la chaîne, elle renvoie une ligne supplémentaire avec 0 pour le type de données BIGINT et » pour VARCHAR.
Par exemple ,
|
1
2
.. 3
4
5
|
DECLARE @user_ids VARCHAR(MAX) = N’203616, 198667, 193718, 188769, 183820, 178871,’
DECLARE @sql_xml XML = Cast(‘<root><U>’+ Replace(@user_ids, ‘,’, ‘</U><U></U></root>’ AS XML)
SELECT f.x.value(‘.’, ‘BIGINT’) AS user_id
From @sql_xml.nodes(‘/root/U’) f(x)
|
Pour gérer ce type de données » ou 0 dans le jeu de résultats, nous pouvons ajouter la condition ci-dessous dans l’instruction T-SQL :
|
1
2
3
4
5
6
|
DECLARE @user_ids VARCHAR(MAX) = N’203616, 198667, 193718, 188769, 183820, 178871,’
DECLARE @sql_xml XML = Cast(‘<root><U>’+ Replace(@user_ids, ‘,’, ‘</U><U></U></root>’ AS XML)
SELECT f.x.value(‘.’, ‘BIGINT’) AS user_id
FROM @sql_xml.nodes(‘/root/U’) f(x)
WHERE f.x.value(‘.’, ‘BIGINT’) <> 0
|
Ici, f.x.value(‘.’, ‘BIGINT’) <> 0 exclut les 0 et f.x.value(‘.’, ‘VARCHAR(100)’) <> » exclut les » dans le jeu de résultats de la requête. Cette condition T-SQL peut être ajoutée dans les fonctions à valeur de table avec le nombre de paramètre d’entrée pour gérer ce délimiteur supplémentaire.
Fonction :
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
CREATE FUNCTION split_string_XML
(
@in_string VARCHAR(MAX),
@delimètre VARCHAR(1)
)
Retourne @list TABLE(tuple VARCHAR(100)).
AS
BEGIN
DECLARE @sql_xml XML = Cast(‘<root><U>’+ Replace(@in_string, @delimite, ‘</U><U></U></root>’ AS XML)
INSERT INTO @list(tuple)
SELECT f.x.value(‘.’, ‘VARCHAR(100)’) AS tuple
FROM @sql_xml.nodes(‘/root/U’) f(x)
WHERE f.x.value(‘.’, ‘BIGINT’) <> 0
RETURN
END
|
Exécution :
|
1
2
|
SELECT *
FROM split_string_XML(‘203616,198667,193718,188769,183820,178871,173922,’, ‘,’)
|
Combinaison de caractères dans une chaîne séparée par un délimiteur
Mais qu’en est-il lorsque le paramètre d’entrée est une combinaison de deux valeurs avec plusieurs séparateurs ? Dans ce cas, le paramètre d’entrée doit être extrait deux fois à l’aide d’une sous-requête, comme indiqué ci-dessous :
|
1
2
3
4
5
6
7
8
9
10
11
12
|
DECLARE @in_string VARCHAR(MAX) = ‘1021|203616$1021|198667$1022|193718$1022|188769 XML = Cast(‘<root><U>’+ Replace(@in_string, ‘ ‘</U><U></U></root>’ AS XML)
SELECT X.Y.value(‘(U)’, ‘VARCHAR(20)’) AS role_id,
X.Y.value(‘(U)’, ‘VARCHAR(20)’) AS user_id
FROM
(
SELECT Cast(‘<root><U>’+ Replace(f.x.value(‘.’, ‘VARCHAR(MAX)’), ‘|’, ‘</U><U></U></root>’ AS XML) AS xml_
FROM @sql_xml.nodes(‘/root/U’) f(x)
WHERE f.x.value(‘.’, ‘VARCHAR(MAX)’) <> »
)T
OUTER APPLY T.xml_.nodes(‘root’) as X(Y)
|
Cet exemple est similaire à l’exemple précédent mais nous avons utilisé pipe « | » comme délimiteur avec un deuxième délimiteur, dollar « $ ». Ici, le paramètre d’entrée est la combinaison de deux colonnes, role_id, et user_id. Comme nous le voyons ci-dessous, user_id et role_id sont dans une colonne séparée dans le jeu de résultats de la table :
Nous pouvons utiliser n’importe quel caractère dans la fonction ci-dessus, comme une seule chaîne séparée par des caractères ou une combinaison de chaînes séparées par des caractères. Un développeur doit simplement remplacer le caractère dans les instructions T-SQL ci-dessus. Suivez les étapes décrites ici pour convertir toute chaîne délimitée en liste. Rappelez-vous simplement que la fonction XML de SQL Server accepte une entrée de chaîne et que la chaîne d’entrée sera délimitée avec le séparateur spécifié à l’aide de la fonction tabulaire.
- Auteur
- Messages récents
Voir tous les messages de Jignesh Raiyani
- L’espérance de vie des pages (PLE) dans SQL Server – 17 juillet, 2020
- Comment automatiser le partitionnement des tables dans SQL Server – 7 juillet 2020
- Configuration des groupes de disponibilité Always On de SQL Server sur AWS EC2 – 6 juillet 2020
.
This example is similar to the above example but we have utilized pipe « |” as a delimiter with a second delimiter, dollar « $”. Here, the input parameter is the combination of two-columns, role_id, and user_id. As we see below, user_id and role_id are in a separate column in the table result set:
We can use any character in the above function, such as a single character-separated string or combination of character-separated strings. A developer must simply replace the character in the T-SQL statements above. Follow the steps described here to convert any delimited string to a list. Simply remember that the XML function of SQL Server accepts a string input and the input string will be delimited with the specified separator using the tabular function.
- Author
- Recent Posts
View all posts by Jignesh Raiyani
- Page Life Expectancy (PLE) in SQL Server – July 17, 2020
- How to automate Table Partitioning in SQL Server – July 7, 2020
- Configuring SQL Server Always On Availability Groups on AWS EC2 – July 6, 2020
, ‘</U><U></U></root>’ AS XML)
This example is similar to the above example but we have utilized pipe « |” as a delimiter with a second delimiter, dollar « $”. Here, the input parameter is the combination of two-columns, role_id, and user_id. As we see below, user_id and role_id are in a separate column in the table result set:
We can use any character in the above function, such as a single character-separated string or combination of character-separated strings. A developer must simply replace the character in the T-SQL statements above. Follow the steps described here to convert any delimited string to a list. Simply remember that the XML function of SQL Server accepts a string input and the input string will be delimited with the specified separator using the tabular function.
- Author
- Recent Posts
View all posts by Jignesh Raiyani
- Page Life Expectancy (PLE) in SQL Server – July 17, 2020
- How to automate Table Partitioning in SQL Server – July 7, 2020
- Configuring SQL Server Always On Availability Groups on AWS EC2 – July 6, 2020
This example is similar to the above example but we have utilized pipe « |” as a delimiter with a second delimiter, dollar « $”. Here, the input parameter is the combination of two-columns, role_id, and user_id. As we see below, user_id and role_id are in a separate column in the table result set:
We can use any character in the above function, such as a single character-separated string or combination of character-separated strings. A developer must simply replace the character in the T-SQL statements above. Follow the steps described here to convert any delimited string to a list. Simply remember that the XML function of SQL Server accepts a string input and the input string will be delimited with the specified separator using the tabular function.
- Author
- Recent Posts
View all posts by Jignesh Raiyani
- Page Life Expectancy (PLE) in SQL Server – July 17, 2020
- How to automate Table Partitioning in SQL Server – July 7, 2020
- Configuring SQL Server Always On Availability Groups on AWS EC2 – July 6, 2020