MS SQL Server
  1. MS SQL Server
  3. string séparée par des virgules en utilisant CTE
Intereting Posts
Pourquoi une table temporaire globale créée par un package SSIS disparaît-elle après l'achèvement? SQL Server – Convertir varchar en un autre classment (page de code) pour corriger le encoding des caractères Génération de documentation pour Power BI Éviter l'enencoding de l'esperluette dans l'atsortingbut SQL XML Obtenir plusieurs lignes à l'aide de la clause FOR JSON Autocomplete sur SQL Management Studio? Comment créer une colonne fictive dans SSIS 2008 Transmettre le nom de DB comme variable dans l'instruction if exists Comment puis-je créer deux commands dans une connection? comment remplacez-vous les valeurs NULL à zéro lors de l'utilisation mule datamapper? Afficher la date la plus récente pour le statut de sortie SQL – comment générer des lignes pour chaque mois en fonction des plages de dates dans un set de données existant? Déclaration du type de variable en fonction d'un type de colonne CakePHP 3 – Impossible de décrire les sessions. Il a 0 colonnes. Serveur SQL SQL Server IF EXISTS

string séparée par des virgules en utilisant CTE

J'ai une string '1,2,3,4,5,6', je veux le résultat dans un tableau comme: 

1 2 3 4 5

Je l'ai essayé en utilisant la fonction et aussi en convertingit au xml.

J'ai une question: 

 with cte1 (str1,str2) AS ( SELECT SUBSTRING('1,2,3,4,5,6,',1,1) X, SUBSTRING('1,2,3,4,5,6,',CHARINDEX(',','1,2,3,4,5,6,,') +1,LEN('1,2,3,4,5,6,')-2) Y UNION all SELECT SUBSTRING(str2,1,1) X ,SUBSTRING(str2,CHARINDEX(',',str2)+1,LEN(str2)-2) Y FROM CTE1 WHERE SUBSTRING(str2,CHARINDEX(',',str2)+0,1) <> ' ' ) SELECT str1 FROM CTE1;

ce qui donne le résultat attendu. mais si je change de string, cela donne des résultats randoms comme: 

 with cte1 (str1,str2) AS ( SELECT SUBSTRING('24,78,45,56,',1,1) X, SUBSTRING('24,78,45,56,',CHARINDEX(',','24,78,45,56,') +1,LEN('24,78,45,56,')-2) Y UNION all SELECT SUBSTRING(str2,1,1) X ,SUBSTRING(str2,CHARINDEX(',',str2)+1,LEN(str2)-2) Y FROM CTE1 WHERE SUBSTRING(str2,CHARINDEX(',',str2)+0,1) <> ' ' ) SELECT str1 FROM CTE1;

résultat : 

 2 7 4 5

entrez la description de l'image ici

Cela ne fonctionnera que si une string est comme '12,34,45,56....' c'est-à-dire que la string contient deux valeurs séparées par des '12,34,45,56....' 

 with cte1 (str1,str2) AS ( SELECT SUBSTRING('24,78,45,56,',1,2) X, SUBSTRING('24,78,45,56,',CHARINDEX(',','24,78,45,56,') +1,LEN('24,78,45,56,')-2) Y UNION all SELECT SUBSTRING(str2,1,2) X ,SUBSTRING(str2,CHARINDEX(',',str2)+1,LEN(str2)-2) Y FROM CTE1 WHERE SUBSTRING(str2,CHARINDEX(',',str2)+0,2) <> ' ' ) SELECT str1 FROM CTE1;

Vous devriez aller avec la solution générique en créant sur l'user define function qui accepte la string séparée par des virgules et donne la valeur de la table pour cette string

Définition de fonction comme ceci 

 CREATE FUNCTION SplitItem( @ItemIDs VARCHAR(MAX)) RETURNS @ItemTable TABLE ( Item VARCHAR(200) ) AS BEGIN DECLARE @Item VARCHAR(200) DECLARE @Index INT WHILE LEN(@ItemIDs) <> 0 BEGIN SET @Index = PATINDEX('%,%', @ItemIDs) IF @Index > 0 BEGIN SET @Item = SUBSTRING(@ItemIDs, 1, @Index - 1) SET @ItemIDs = RIGHT(@ItemIDs, LEN(@ItemIDs) - @Index) INSERT INTO @ItemTable VALUES ( @Item ) END ELSE BEGIN BREAK END END SET @Item = @ItemIDs INSERT INTO @ItemTable VALUES ( @Item ) RETURN END

Et utilisez cette fonction comme ça 

 SELECT Item FROM SplitItem('1,2,3,44,55,66,77')

Cela donnera des résultats comme celui-ci

1 2 3 44 55 66 77

Vous pourriez utiliser un CTE récursif 

 Declare @list NVARCHAR(MAX) = '1,2,3,4,5' DECLARE @length INT = LEN(@list) + 1; WITH a AS ( SELECT [start] = 1, [end] = COALESCE(NULLIF(CHARINDEX(',', @List, 1), 0), @length), [value] = SUBSTRING(@list, 1, COALESCE(NULLIF(CHARINDEX(',', @List, 1), 0), @length) - 1) UNION ALL SELECT [start] = CONVERT(INT, [end]) + 1, [end] = COALESCE(NULLIF(CHARINDEX(',', @list, [end] + 1), 0), @length), [value] = SUBSTRING(@list, [end] + 1, COALESCE(NULLIF(CHARINDEX(',', @list, [end] + 1), 0), @length)-[end]-1) FROM a WHERE [end] < @length ) SELECT [value] FROM a WHERE LEN([value]) > 0 OPTION (MAXRECURSION 0);

Vous pouvez faire quelque chose comme ceci: 

 DECLARE @ssortingng NVARCHAR(MAX) = '1,2,3,4,5,6,', @xml xml select @xml = cast('<d><q>'+REPLACE(@ssortingng,',','</q><q>')+'</q></d>' as xml) SELECT nvvalue('.','nvarchar(2)') FROM @xml.nodes('/d/q') AS n(v);

Le résultat: 

 ---- 1 2 3 4 5 6 (7 row(s) affected)