Nitin,

The short answer to your question is to use DelimitedSplit8K[/url] to split the text strings into different columns and then deal with those.

This was my first try splitting (just so you can see how to use the function... but you have to read the article and follow the instructions to create the function in your own database first...)

SELECT x.theFileName

, MIN(CASE WHEN x.Item LIKE 'Bytes%' THEN RIGHT(x.Item,LEN(x.Item)-CHARINDEX(':',x.Item,1)) END) AS Bytes

, MIN(CASE WHEN x.Item LIKE 'Records%' THEN RIGHT(x.Item,LEN(x.Item)-CHARINDEX(':',x.Item,1)) END) AS Records

FROM

( SELECT test.[FileName] AS theFileName

, split.ItemNumber

, split.Item

FROM control_Total test

CROSS APPLY MyDatabase.dbo.DelimitedSplit8k(test.[FileName],' ') split) x

WHERE x.Item LIKE 'Bytes%' OR x.Item LIKE 'Records%'

GROUP BY x.theFileName;

I had to use MIN/GROUP BY because the DelimitedSplit8K function returns a table, and populates the table with the individual values that get split out of the input, in this case theFileName (I changed it because FileName is a reserved word in T-SQL.)

Hope this helps!

Pieter