J'ai un exemple de données comme celui-ci
Date Name Req Code 8/8/2018 23:02:57,731 INFO [AllRequestInterceptor] CRTST020 8/8/2018 23:03:11,687 INFO [SOAPLoggingHandler] CRTST020 8/8/2018 23:03:02,028 ERROR [AJAXController] CRTST003
J'essaie de créer 4 colonnes en fonction des espaces fournis.
SELECT Reverse(ParseName(Replace(Reverse([name]), ' ', '.'), 1)) As [M1] ,Reverse(ParseName(Replace(Reverse([name]), ' ', '.'), 2)) As [M2] ,Reverse(ParseName(Replace(Reverse([name]), ' ', '.'), 3)) As [M3] ,Reverse(ParseName(Replace(Reverse([name]), ' ', '.'), 4)) As [M4] FROM (Select [name] from @Table1 ) As [x]
Résultat attendu:
DECLARE @Table1 table ([name] varchar(62)); INSERT INTO @Table1 ([name]) VALUES ('2018-08-08 23:02:57,731 INFO [AllRequestInterceptor] CRTST020'), ('2018-08-08 23:03:11,687 INFO [SOAPLoggingHandler] CRTST020'), ('2018-08-08 23:03:02,028 ERROR [AJAXController] CRTST003');
4 Réponses :
Vos données sont presque un format de longueur fixe. Pour vos exemples de données, vous pouvez utiliser des fonctions de chaîne:
select left(name, 23) as date, trim(substring(name, 25, 6)) as name, trim(substring(name, 31, len(name) - 39)) as req, right(name, 8) as code from @table1 t1;
Ici est un violon db .
Gordon Je viens de publier des exemples de disques. Nous ne sommes pas sûrs d’obtenir la même durée à chaque fois
Ce n'est pas idéal, mais en supposant que le seul endroit où un espace apparaîtra (autre que comme délimiteur) dans la valeur de date
, vous pouvez utiliser un séparateur de chaîne, puis faites pivoter les données en arrière. Cela utilise delimitedsplit8k_LEAD
, car la position ordinale est importante:
DECLARE @Table1 table ([name] varchar(62)); INSERT INTO @Table1 ([name]) VALUES ('2018-08-08 23:02:57,731 INFO [AllRequestInterceptor] CRTST020'), ('2018-08-08 23:03:11,687 INFO [SOAPLoggingHandler] CRTST020'), ('2018-08-08 23:03:02,028 ERROR [AJAXController] CRTST003'); DECLARE @Delimiter char(1) = ' '; WITH E1 (N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1), --10E+1 or 10 rows E2 (N) AS (SELECT 1 FROM E1 AS a, E1 AS b), --10E+2 or 100 rows E4 (N) AS (SELECT 1 FROM E2 AS a, E2 AS b), --10E+4 or 10,000 rows max cteTally (N) AS ( --==== This provides the "zero base" and limits the number of rows right up front -- for both a performance gain and prevention of accidental "overruns" SELECT 0 UNION ALL SELECT TOP 62 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4), cteStart (N1, [name]) AS ( --==== This returns N+1 (starting position of each "element" just once for each delimiter) SELECT t.N + 1, T1.[name] FROM cteTally AS t CROSS JOIN @Table1 AS T1 WHERE (SUBSTRING(T1.[name], t.N, 1) = @Delimiter OR t.N = 0)), Splits AS (SELECT s.[name], ROW_NUMBER() OVER (ORDER BY s.N1) AS ItemNumber, SUBSTRING(s.[name], s.N1, ISNULL(NULLIF((LEAD(s.N1, 1, 1) OVER (PARTITION BY s.[name] ORDER BY s.N1) - 1), 0) - s.N1, 8000)) AS item FROM cteStart AS s), CTE AS (SELECT name, ItemNumber, item, ROW_NUMBER() OVER (PARTITION BY [name] ORDER BY ItemNumber ASC) AS RN FROM Splits WHERE item <> '') SELECT MAX(CASE WHEN RN = 1 THEN item END) + ' ' + MAX(CASE WHEN RN = 2 THEN item END) AS [Date], MAX(CASE WHEN RN = 3 THEN item END) AS [Name], MAX(CASE WHEN RN = 4 THEN item END) AS Req, MAX(CASE WHEN RN = 5 THEN item END) AS Code FROM CTE GROUP BY [name];
Blarg, si vous ne pouvez pas créer la fonction, vous pourriez le faire ( mais beurk):
WITH CTE AS( SELECT T1.name, DS.ItemNumber, DS.Item, ROW_NUMBER() OVER (PARTITION BY T1.[name] ORDER BY DS.ItemNumber ASC) AS RN FROM @Table1 T1 CROSS APPLY dbo.delimitedsplit8k_LEAD(T1.[name],' ') DS WHERE DS.Item <> '') SELECT MAX(CASE WHEN RN = 1 THEN Item END) + ' ' + MAX(CASE WHEN RN = 2 THEN Item END) AS [Date], MAX(CASE WHEN RN = 3 THEN Item END) AS [Name], MAX(CASE WHEN RN = 4 THEN Item END) AS Req, MAX(CASE WHEN RN = 5 THEN Item END) AS Code FROM CTE GROUP BY [Name];
merci d'avoir répondu Je n'ai pas la permission de créer la fonction dbo.delimitedsplit8k_LEAD dans ma base de données
Eh bien, étant donné que vos données ne peuvent pas être normalisées pour avoir une largeur fixe (comme Gordan a emprunté cette voie), vous allez vraiment avoir du mal. vous devrez probablement écrire le code pour delimitedsplit8K_LEAD
dans votre SQL, plutôt que de l'appeler comme une fonction; loin d'être idéal, mais les données non plus.
Essayez ceci
Date Name Req Code ---------------------------------------------------------------- 2018-08-08 23:02:57,731 INFO [AllRequestInterceptor] CRTST020 2018-08-08 23:03:02,028 ERROR [AJAXController] CRTST003 2018-08-08 23:03:11,687 INFO [SOAPLoggingHandler] CRTST020
Résultat
DECLARE @Table1 table ([name] varchar(1000)); INSERT INTO @Table1 ([name]) VALUES ('2018-08-08 23:02:57,731 INFO [AllRequestInterceptor] CRTST020'), ('2018-08-08 23:03:11,687 INFO [SOAPLoggingHandler] CRTST020'), ('2018-08-08 23:03:02,028 ERROR [AJAXController] CRTST003'); SELECT DISTINCT Split.a.value('/S[1]', 'NVARCHAR(MAX)')+' '+ Split.a.value('/S[2]', 'NVARCHAR(MAX)') [Date], Split.a.value('/S[3]', 'NVARCHAR(MAX)') As Name, Split.a.value('/S[4]', 'NVARCHAR(MAX)') As Req, Split.a.value('/S[5]', 'NVARCHAR(MAX)') As Code FROM ( SELECT CAST('<S>'+REPLACE([name] ,' ','</S><S>' ) +'</S>' AS XML) AS [name] FROM @Table1 ) AS A CROSS APPLY [name].nodes('S') AS Split(a)
Vous pouvez utiliser plusieurs apply
pour cela:
select substring(name, 0, n) as [Date], substring(d, 0, d1) as Name, substring(d, d1, d2-d1+1) as Req, substring(d, d2+1, len(name)) as Code from @Table1 t1 cross apply ( values (patindex('%[a-z]%', name)) ) tt(n) cross apply ( values (substring(name, n, len(name))) ) ttt(d) cross apply ( values (charindex('[', d), charindex(']', d)) ) tttt(d1, d2);
Vous avez plusieurs espaces dans vos valeurs, et certains ne sont pas des délimiteurs (par exemple l'espace dans
2018-08-08 23: 02: 57,731
dans un délimiteur). Ce n'est pas non plus une largeur fixe. Vous devez utiliser un délimiteur qui n'apparaît pas dans vos données ou choisir une autre méthode.@Larnu même si les données ont des espaces, nous devrions les considérer comme une seule colonne et le reste de tous doit diviser par des espaces
Req
pourrait-il avoir un espace? Qu'en est-il duCode
? Comment savez-vous où commence une colonne si les éléments qui l’identifient ne peuvent pas signifier qu’elle commence?oui @Larnu tout doit être divisé par un espace faire une exception pour l'espace de colonne de date