Do you mean a database file or just a string that contains a filename. To remove the path and the extension, you could try something like:
declare @filename varchar(50);
set @filename = 'c:\a\b\c\filename.extension';
set @filename = substring( @filename, 1, charindex( '.', @filename) - 1)
while charindex( '\', @filename) > 0
begin
set @filename = substring( @filename, charindex( '\', @filename) + 1, 999999)
end
select @filename
And if you're interested in files of a database you should have a look at sys.database_files[^]
The need to optimize rises from a bad design.My articles[^]
