Very Computer

Hi,

I have a table of the following structure

tblFileNames (FileID int, FileName nvarchar(255))

FileName may contain file path information (Like X:\Xdir\myfile.doc or
X:\Xdir\myfile.document.jpeg etc.)

I need to write a Select statement that would return

FileID          FileName                                File Extension.
--------------- ---------------------------------------
---------------
1               X:\Xdir\myfile.doc                      doc
2               X:\Xdir\myfile.document.jpeg            jpeg

Any suggestion?

select FileID,FileName,
RIGHT(Filename,(CHARINDEX('.',REVERSE(Filename))-1)) as 'Extension'
from tblFileNames

If in 2000 you cound create a UDF e.g.

returns nvarchar(10)
as
begin

end

and then your statement is

select FileID,FileName,dbo.getfilext(FileName) as 'Extension'
from tblFileNames

HTH
Jasper Smith

Quote:> Hi,

> I have a table of the following structure

> tblFileNames (FileID int, FileName nvarchar(255))

> FileName may contain file path information (Like X:\Xdir\myfile.doc or
> X:\Xdir\myfile.document.jpeg etc.)

> I need to write a Select statement that would return

> FileID FileName File Extension.
> --------------- ---------------------------------------
> ---------------
> 1 X:\Xdir\myfile.doc doc
> 2 X:\Xdir\myfile.document.jpeg         jpeg

> Any suggestion?

This code does not work when file name does not contain any dot, then
(CHARINDEX('.',REVERSE(Filename))-1) becomes -1 and RIGHT function fails.