TSQL Substring

SELECT str1,
    LEFT(ending_string, CHARINDEX('.', ending_string) - 1) AS result
FROM (
    SELECT '\\Myfolder\File.MyFile1.csv' AS str1 UNION ALL
    SELECT '\\Myfolder\Folder1\subfolder2\filename.MyFile1.csv' UNION ALL
    SELECT 'c:\Myfolder\Folder1\sub.fol.der2\fi.l.e.name.MyFile1.csv'
) AS test_data
CROSS APPLY (
    SELECT CHARINDEX('.', REVERSE(str1)) AS last_period
) AS assign_alias_names1
CROSS APPLY (
    SELECT RIGHT(str1, CHARINDEX('.', REVERSE(str1), last_period + 1) - 1) AS ending_string
) AS assign_alias_names2

I don't have a way to test but, in pseudo code, reverse the string and take the substring of that starting with the index of the (now first) '.' + 1, then take the substring of that up until the index of '.' -1, then reverse the result.

Its not always ".csv" extension but it will have file extension of 4 character including the "."

Then you just need to replace the '.csv' with the desired extension.

Still have no means of testing but, trying to add syntax (assumes 3-character file extensions -
select reverse(substring(reverse(substring(str1,1,len(str1) - 4)),1,charindex('.',reverse(substring(str1,1,len(str1) - 4) - 1)))

Did you look at the first code I posted?  It has no dependency on any specific extension.

Scott,
The -4 that I used to manage a 3-character extension could be substituted with additional charindex statements which you code addressed. I was just trying to reach a solution with just one select statement.

It is one SELECT statement; the other SELECTs just provide test data, which you would replace with your own table name.

The CROSS APPLYs are part of the SELECT, and just reduce the complexity of the statement so it can be maintained later.  But, if you don't want those, then, yes, you could use Vitor's massively imbedded functions method.

Thanks