Version finale (j'espère) :
Puisque sql server 2008 ne supporte pas l'ordre par dans la clause over des fonctions agrégées, j'ai ajouté un autre cte pour ajouter l'index de la ligne au lieu de l'index de la ligne. sum que j'ai utilisé dans la version précédente :
;WITH cteAllRows as
(
SELECT Item,
ItemIndex,
CASE WHEN ISNUMERIC(Item) = 0 THEN 'String'
WHEN ISNUMERIC(Item) = 1 AND CHARINDEX('.', Item) > 0 THEN 'Double'
WHEN ISNUMERIC(Item) = 1 AND CHARINDEX('.', Item) = 0 THEN 'Integer'
END As DataType
FROM dbo.SplitStrings_Numbers(@string, ',')
), cteAll as
(
SELECT Item,
DataType,
ItemIndex,
(
SELECT COUNT(*)
FROM cteAllRows tInner
WHERE tInner.DataType = 'String'
AND tInner.ItemIndex <= tOuter.ItemIndex
) As RowIndex
FROM cteAllRows tOuter
)
Tout le reste est identique à la version précédente.
Mise à jour
La première chose que j'ai faite est de changer la fonction de division de la chaîne de caractères en une fonction basée sur une table de pointage, de sorte que je puisse facilement y ajouter le numéro de ligne. Donc, si vous n'avez pas déjà une table de pointage, créez-en un . Si vous vous demandez ce qu'est une table de pointage et pourquoi vous en avez besoin, lire cet article de Jeff Moden :
SELECT TOP 10000 IDENTITY(int,1,1) AS Number
INTO Tally
FROM sys.objects s1
CROSS JOIN sys.objects s2
ALTER TABLE Tally ADD CONSTRAINT PK_NumbersTest PRIMARY KEY CLUSTERED (Number)
GO
Ensuite, créez une fonction de fractionnement de chaîne basée sur la table de pointage (reprise de l'article d'Aaron mais en ajoutant la colonne d'index de ligne) :
CREATE FUNCTION dbo.SplitStrings_Numbers
(
@List NVARCHAR(MAX),
@Delimiter NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
SELECT Item = SUBSTRING(@List, Number, CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number),
ROW_NUMBER() OVER (ORDER BY Number) As ItemIndex
FROM dbo.Tally
WHERE Number <= CONVERT(INT, LEN(@List))
AND SUBSTRING(@Delimiter + @List, Number, LEN(@Delimiter)) = @Delimiter
);
GO
Maintenant, l'astuce que j'ai utilisée ressemble beaucoup à la précédente, mais j'ai ajouté à la première colonne une nouvelle colonne que j'ai appelée RowIndex, qui est essentiellement un total courant du nombre de chaînes de caractères, basé sur l'indice de ligne de toutes les lignes :
SELECT Item,
CASE WHEN ISNUMERIC(Item) = 0 THEN 'String'
WHEN ISNUMERIC(Item) = 1 AND CHARINDEX('.', Item) > 0 THEN 'Double'
WHEN ISNUMERIC(Item) = 1 AND CHARINDEX('.', Item) = 0 THEN 'Integer'
END As DataType,
SUM(CASE WHEN ISNUMERIC(Item) = 0 THEN 1 END) OVER(ORDER BY ItemIndex) As RowIndex
FROM dbo.SplitStrings_Numbers(@string, ',')
Cela m'a donné ce résultat :
Item DataType RowIndex
---------- -------- -----------
ddd String 1
1.5 Double 1
1 Integer 1
eee String 2
2.3 Double 2
0 Integer 2
fff String 3
1.2 Double 3
ggg String 4
6.123 Double 4
1 Integer 4
Comme vous pouvez le voir, j'ai maintenant un numéro pour chaque ligne, donc à partir de maintenant c'est simple :
;WITH cteAll as
(
SELECT Item,
CASE WHEN ISNUMERIC(Item) = 0 THEN 'String'
WHEN ISNUMERIC(Item) = 1 AND CHARINDEX('.', Item) > 0 THEN 'Double'
WHEN ISNUMERIC(Item) = 1 AND CHARINDEX('.', Item) = 0 THEN 'Integer'
END As DataType,
SUM(CASE WHEN ISNUMERIC(Item) = 0 THEN 1 END) OVER(ORDER BY ItemIndex) As RowIndex
FROM dbo.SplitStrings_Numbers(@string, ',')
), cteString AS
(
SELECT Item, RowIndex
FROM cteAll
WHERE DataType = 'String'
), cteDouble AS
(
SELECT Item, RowIndex
FROM cteAll
WHERE DataType = 'Double'
), cteInteger AS
(
SELECT Item, RowIndex
FROM cteAll
WHERE DataType = 'Integer'
)
SELECT T1.Item As [String],
T2.Item As [Double],
T3.Item As [Integer]
FROM dbo.Tally
LEFT JOIN cteString T1 ON T1.RowIndex = Number
LEFT JOIN cteDouble T2 ON t2.RowIndex = Number
LEFT JOIN cteInteger T3 ON t3.RowIndex = Number
WHERE COALESCE(T1.Item, T2.Item, T3.Item) IS NOT NULL
Cela m'a donné ce résultat :
String Double Integer
---------- ---------- ----------
ddd 1.5 1
eee 2.3 0
fff 1.2 NULL
ggg 6.123 1
Comme vous pouvez le constater, les éléments sont maintenant triés selon l'ordre original de la chaîne. Merci pour le défi, cela fait un moment que je n'en ai pas eu un décent :-)
Première tentative
Eh bien, d'abord vous devez diviser cette chaîne en un tableau. Pour ce faire, vous devez utiliser une fonction définie par l'utilisateur. Vous pouvez choisir celle qui vous convient le mieux parmi celles proposées par Aaron Bertrand. Séparer les cordes de la bonne façon - ou de la meilleure façon suivante article.
Pour cette démonstration, j'ai choisi d'utiliser la fonction SplitStrings_XML .
Donc d'abord, créer la fonction :
CREATE FUNCTION dbo.SplitStrings_XML
(
@List NVARCHAR(MAX),
@Delimiter NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
FROM
(
SELECT x = CONVERT(XML, '<i>'
+ REPLACE(@List, @Delimiter, '</i><i>')
+ '</i>').query('.')
) AS a CROSS APPLY x.nodes('i') AS y(i)
);
GO
Maintenant, déclarez et initialisez la variable :
declare @string nvarchar(max) = 'ddd,1.5,1,eee,2.3,0,fff,1.2,ggg,6.123,1'
Ensuite, créez 4 expressions courantes des tableaux - un pour tous les éléments, un pour les chaînes de caractères, un pour les doubles et un pour les entiers. Notez l'utilisation de l'élément row_number() qui sera utilisée plus tard pour regrouper tous les résultats :
;WITH AllItems as
(
SELECT Item, ROW_NUMBER() OVER(ORDER BY (select null)) as rn
FROM dbo.SplitStrings_XML(@string, ',')
)
, Strings as
(
SELECT Item as StringItem, ROW_NUMBER() OVER(ORDER BY (select null)) as rn
FROM dbo.SplitStrings_XML(@string, ',')
WHERE ISNUMERIC(Item) = 0
), Doubles as
(
SELECT Item as DoubleItem, ROW_NUMBER() OVER(ORDER BY (select null)) as rn
FROM dbo.SplitStrings_XML(@string, ',')
WHERE ISNUMERIC(Item) = 1 AND CHARINDEX('.', Item) > 0
), Integers as
(
SELECT Item as IntegerItem, ROW_NUMBER() OVER(ORDER BY (select null)) as rn
FROM dbo.SplitStrings_XML(@string, ',')
WHERE ISNUMERIC(Item) = 1 AND CHARINDEX('.', Item) = 0
)
Ensuite, sélectionnez en joignant toutes ces expressions de table communes. Notez l'utilisation de l'élément COALESCE pour ne retourner que les lignes où au moins une valeur est présente :
SELECT StringItem, DoubleItem, IntegerItem
FROM AllItems A
LEFT JOIN Strings S ON A.rn = S.rn
LEFT JOIN Doubles D ON A.rn = D.rn
LEFT JOIN Integers I ON A.rn = I.rn
WHERE COALESCE(StringItem, DoubleItem, IntegerItem) IS NOT NULL
Résultats :
StringItem DoubleItem IntegerItem
---------- ---------- -----------
ddd 1.5 1
eee 2.3 0
fff 1.2 1
ggg 6.123 NULL
