create table #temp
(
id int,
name varchar(100) null,
)
insert into #temp values(1,'c:\temp\abc.jpg')
insert into #temp values(2,'c:\temp\aDc.jpg')
insert into #temp values(3,'c:\temp\aec.jpg')
insert into #temp values(4,'c:\temp\afc.jpg')
select RIGHT(name, CHARINDEX('\', REVERSE(name)) -1) [file_name],
SUBSTRING(name,len(name)-2,3),* from #temp where CHARINDEX('jpg',name) > 0
