Declare @Filename varchar(100) = 'C:UsersakumarDesktop_Claim FilesV2_MEDICA_PHA_CLAIMS_YYYYMMDD.txt'

select substring(@filename,1,len(@filename) - charindex('_',REVERSE(@filename)))