STRING_SPLIT (Transact-SQL)

  • Article

S’applique à : SQL Server 2016 (13.x) et versions ultérieuresAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsPoint de terminaison analytique SQL dans Microsoft FabricEntrepôt dans Microsoft Fabric

STRING_SPLIT est une fonction table qui divise une chaîne en lignes de sous-chaînes, en fonction d’un caractère de séparation spécifié.

Niveau de compatibilité 130

STRING_SPLIT nécessite que le niveau de compatibilité soit au moins 130. Lorsque le niveau est inférieur à 130, le moteur de base de données ne peut pas trouver la fonction STRING_SPLIT.

Pour changer le niveau de compatibilité d’une base de données, consultez Afficher ou changer le niveau de compatibilité d’une base de données.

Remarque

La configuration de la compatibilité n’est pas nécessaire pour STRING_SPLIT dans Azure Synapse Analytics.

Conventions de la syntaxe Transact-SQL

Syntaxe

STRING_SPLIT ( string , separator [ , enable_ordinal ] )

Arguments

string

Une expression de n’importe quel type de caractère (par exemple, nvarchar, varchar, nchar ou char).

separator

Une expression d’un seul caractère de n’importe quel type de caractère (par exemple nvarchar(1) , varchar(1) , nchar(1) ou char(1) ) qui est utilisée comme séparateur pour les sous-chaînes concaténées.

enable_ordinal

S’applique à : Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics (pool SQL serverless uniquement) et SQL Server 2022 (16.x) et versions ultérieures

Expressionint ou bit qui sert d’indicateur pour activer ou désactiver la colonne de sortie ordinal. La valeur de 1 active la colonne ordinal. Si enable_ordinal est omis, NULL, ou a une valeur de 0, la colonne ordinal est désactivée.

Types de retour

Si la colonne de sortie ordinal n’est pas activée, STRING_SPLIT retourne une table à une seule colonne dont les lignes sont les substrings. Le nom de la colonne est value. Elle retourne nvarchar si un des arguments d’entrée est de type nvarchar ou nchar. Sinon, elle retourne varchar. La longueur du type de retour est identique à la longueur de l’argument de chaîne.

Si l’argument enable_ordinal a dépassé une valeur de 1, une deuxième colonne nommée ordinal est retournée, composée des valeurs d’index de base 1 de la position de chaque substring dans la chaîne d’entrée. Le type de retour est une bigint.

Notes

STRING_SPLIT entre une chaîne qui a des substrings délimitées et un caractère à utiliser comme délimiteur ou séparateur. Si vous le souhaitez, la fonction prend en charge un troisième argument avec une valeur de 0 ou 1 qui désactive ou active, respectivement, la colonne de sortie ordinal.

STRING_SPLIT génère une table à une ou deux colonnes, selon l’argument enable_ordinal.

  • Si enable_ordinal est NULL, omis ou a la valeur 0, STRING_SPLIT retourne une table à une seule colonne dont les lignes contiennent les substrings. Le nom de la colonne de sortie est value.

  • Si enable_ordinal a une valeur de 1, la fonction retourne une table à deux colonnes, y compris la colonne ordinal qui se compose des valeurs d’index de base 1 des substrings dans la chaîne d’entrée d’origine.

L’argument enable_ordinal doit être une valeur constante et non une colonne ou une variable. Il doit également s’agir d’un type de données bit ou int avec la valeur de 0 ou 1. Dans le cas contraire, la fonction génère une erreur.

Les lignes résultantes peuvent être dans n’importe quel ordre. Il n’est pas garanti que l’ordre corresponde à l’ordre des sous-chaînes dans la chaîne en entrée. Vous pouvez remplacer l’ordre de tri final en utilisant une clause ORDER BY sur l’instruction SELECT, par exemple ORDER BY value ou ORDER BY ordinal.

0x0000 (char(0)) est un caractère non défini dans les classements Windows, qui n’est pas utilisable avec STRING_SPLIT.

Les sous-chaînes vides de longueur nulle sont présentes quand la chaîne en entrée contient plusieurs occurrences consécutives du caractère délimiteur. Les sous-chaînes vides sont traitées de la même façon que les sous-chaînes contenant du texte. Vous pouvez filtrer les lignes contenant la substring vide en utilisant la clause WHERE, par exemple WHERE value <> ''. Si la chaîne en entrée est NULL, la Fonction table STRING_SPLIT retourne une table vide.

Par exemple, l’instruction SELECT suivante utilise le caractère espace comme séparateur :

SELECT value FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ');

À titre d’exemple, l’instruction SELECT précédente a retourné la table de résultats suivante :

value
Lorem
ipsum
dolor
sit
amet.

L’exemple suivant active la colonne ordinal en transmettant 1 pour le troisième argument facultatif :

SELECT * FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ', 1);

Cette instruction retourne ensuite la table de résultats suivante :

value ordinal
Lorem 1
ipsum 2
dolor 3
sit 4
amet. 5

Exemples

R. Diviser une chaîne de valeurs séparées par des virgules

Analysez une liste de valeurs séparées par des virgules et retournez tous les jetons non vides :

DECLARE @tags NVARCHAR(400) = 'clothing,road,,touring,bike'

SELECT value
FROM STRING_SPLIT(@tags, ',')
WHERE RTRIM(value) <> '';

STRING_SPLIT retourne une chaîne vide si aucun élément ne figure entre les séparateurs. La condition RTRIM(value) <> '' supprime les jetons vides.

B. Diviser une chaîne de valeurs séparées par des virgules dans une colonne

La table de produits a une colonne avec une liste de balises séparées par des virgules, illustrée dans l’exemple suivant :

ProductId Nom Les indicateurs
1 Full-Finger Gloves clothing,road,touring,bike
2 LL Headset bike
3 HL Mountain Frame bike,mountain

La requête suivante transforme chaque liste de balises et les joint à la ligne d’origine :

SELECT ProductId, Name, value
FROM Product
    CROSS APPLY STRING_SPLIT(Tags, ',');

Voici le jeu de résultats obtenu.

ProductId Nom Valeur
1 Full-Finger Gloves clothing
1 Full-Finger Gloves road
1 Full-Finger Gloves touring
1 Full-Finger Gloves bike
2 LL Headset bike
3 HL Mountain Frame bike
3 HL Mountain Frame mountain

Remarque

L’ordre de la sortie peut varier, car il n’est pas garanti que l’ordre corresponde à l’ordre des sous-chaînes dans la chaîne d’entrée.

C. Agrégation par valeurs

Les utilisateurs doivent créer un rapport qui indique le nombre de produits pour chaque balise, classés par nombre de produits, afin de filtrer uniquement les balises avec plus de deux produits.

SELECT value as tag, COUNT(*) AS [number_of_articles]
FROM Product
    CROSS APPLY STRING_SPLIT(Tags, ',')
GROUP BY value
HAVING COUNT(*) > 2
ORDER BY COUNT(*) DESC;

D. Rechercher par valeur de balise

Les développeurs doivent créer des requêtes qui recherchent les articles par mots clés. Ils peuvent utiliser les requêtes suivantes :

Pour rechercher les produits avec une seule balise (clothing) :

SELECT ProductId, Name, Tags
FROM Product
WHERE 'clothing' IN (SELECT value FROM STRING_SPLIT(Tags, ','));

Recherchez les produits avec deux balises spécifiées (clothing et road) :

SELECT ProductId, Name, Tags
FROM Product
WHERE EXISTS (SELECT *
    FROM STRING_SPLIT(Tags, ',')
    WHERE value IN ('clothing', 'road'));

E. Rechercher des lignes par liste de valeurs

Les développeurs doivent créer une requête qui recherche des articles selon une liste d’ID. Ils peuvent utiliser la requête suivante :

SELECT ProductId, Name, Tags
FROM Product
JOIN STRING_SPLIT('1,2,3',',')
    ON value = ProductId;

L’utilisation de STRING_SPLIT précédente est un remplacement pour un anti-modèle courant. Un tel anti-modèle peut impliquer la création d’une chaîne SQL dynamique dans la couche application ou dans Transact-SQL. Un anti-modèle peut aussi être obtenu avec l’opérateur LIKE. Considérez l’exemple d’instruction SELECT suivante :

SELECT ProductId, Name, Tags
FROM Product
WHERE ',1,2,3,' LIKE '%,' + CAST(ProductId AS VARCHAR(20)) + ',%';

F. Rechercher des lignes par valeurs ordinales

L’instruction suivante recherche toutes les lignes avec une valeur d’index pair :

SELECT *
FROM STRING_SPLIT('Austin,Texas,Seattle,Washington,Denver,Colorado', ',', 1)
WHERE ordinal % 2 = 0;

L’instruction ci-dessus retourne la table suivante :

value ordinal
Texas 2
Washington 4
Colorado 6

G. Classer des lignes par valeurs ordinales

L’instruction suivante retourne les valeurs de substrings de fractionnement de la chaîne d’entrée et leurs valeurs ordinales, classées par la colonne ordinal :

SELECT * FROM STRING_SPLIT('E-D-C-B-A', '-', 1) ORDER BY ordinal DESC;

L’instruction ci-dessus retourne la table suivante :

value ordinal
A 5
B 4
C 3
D 2
E 1

Contenu connexe