DECLARE @StringValue VARCHAR(MAX) = '10/06/2009 01:23 PM 1,089,024 20067e8a.msi
11/30/2010 06:07 AM 62,976 227b809e.msi
07/10/2008 12:18 PM 5,478,400 38d6a0.msi
10/22/2010 06:32 AM 16,290,304 402d614c.msi
10/22/2010 06:44 AM 32,274,432 402d6256.msi
03/30/2009 06:52 PM 24,970,752 52f140.msp
08/25/2010 09:26 AM 172,032 5a711f8b.msi
08/25/2010 09:26 AM 573,952 5a711f8f.msi
08/25/2010 09:28 AM 347,136 5a711fc8.msi
09/10/2009 06:48 AM 328,192 611ec7c.msi
12/18/2010 08:43 PM 7,877,632 8239bd81.msi
12/18/2010 08:54 PM 1,856,512 8239bd90.msi
09/02/2010 07:05 AM 1,647,104 83238c3c.msi
11/09/2010 10:26 AM 1,972,736 9db434f8.msi
12/18/2010 08:43 PM {355EFEB9-CCC8-4D16-8457-B683C7B675A2}
08/25/2010 09:28 AM {36A415C2-7181-421D-92C9-8255766E0FF3}
10/22/2010 06:32 AM {83E954A5-4BD0-4C18-BDC6-63BB4AA23EFA}
10/22/2010 06:45 AM {A1C9093D-CD66-4B0B-B890-4DE5E2EC5428}
12/18/2010 08:54 PM {C3AF5BD8-30D5-41F5-AF61-705D98146B0F}
08/25/2010 09:26 AM {CA206913-EE9F-495F-AD43-032E5833EE13}'
SELECT
FileDate,
CASE WHEN Field2 LIKE '%%' THEN 1 ELSE 0 END AS Directory,
CASE WHEN Field2 NOT LIKE '%%' THEN CAST(REPLACE(FIELD2, ',', '') AS BIGINT) / 1024 END AS FileSizeKB,
Field3 AS Name
FROM Util.dbo.ParseDelimited(@StringValue, '
')
CROSS APPLY(SELECT CAST(SUBSTRING(Field, 1, 20) AS SMALLDATETIME) AS FileDate,
SUBSTRING(Field, 21, 18) AS Field2,
SUBSTRING(Field, 40, 500) AS Field3) b