extracting from a string value

I have something like this

E:\path1\subpath\Summary Report.xml in a database column

And what I want to pull out of the string is

Summary Report

How can I pull this out with the only thing that is constant is that the string is always between the last back slash and the .XML extension.

Code Snippet

string text = objRow("Path").ToString();
string fileName = text.Substring(text.LastIndexOf("\") + 1);
fileName = fileName.Replace(".xml", string.Empty); //this could also be a substring operation if the extension changes

|||I need to use T-SQL to do it|||

Another way that is a bit over-the-top is something like:

Code Snippet

select reverse(replace(reverse(rtrim(right('E:\path1\subpath\Summary Report.xml ', charindex('\', reverse('E:\path1\subpath\Summary Report.xml '))-1))), 'lmx.', '')) as theFile

/*
theFile
--
Summary Report
*/

|||

something like this works:

Code Snippet

declare @.path varchar(50)

set @.path ='E:\path1\subpath\Summary Report.xml'

selectsubstring(@.path,len(@.path)-charindex('\',reverse(@.path))+2,

charindex('\',reverse(@.path))-5)

|||

Try:

declare @.s varchar(50)

set @.s ='E:\path1\subpath\Summary Report.xml'

selectsubstring(@.s,len(@.s)-patindex('%\%',reverse(@.s))+ 2,patindex('%\%',reverse(@.s))- 5)

AMB