T-SQL Get File Extension Name from a Column

Question

I have a table with a column that contains the entire file name, the data looks like:

FilePath                       FileSize
------------------------------ --------
dsch2_chs_wyj.rar              694KB
AllInOneHash.rar               19KB
FilePropertyModifier.rar       12KB
jquery_1_7_api_chm_chs.rar     285KB
startupmgr.rar                 38KB
JQueryTableExample_Edi.rar     33KB
hdpartioncalc_csharp.rar       49KB
XMLMenuTest.zip                3KB

Now I want to extract the file extension name, like .rar and insert into a new table.

INSERT INTO Attachment
  (
    Id,
    [FileName],
    ExtensionName,
    StoredPath,
    CreateOn,
    UploaderIP,
    DataBinary
  )
SELECT ba.Id,
       ba.Title,
       '{Extension Name}',
       ba.FilePath,
       GETDATE(),
       NULL,
       NULL
FROM   BlogAttachment ba

But T-SQL doesn't have a LastIndexOf() function. How can I cut the substring in an easy way?

Answer 1

You can get the extension by doing:

 select reverse(left(reverse(FilePath), charindex('.', reverse(FilePath)) - 1))

However, I would recommend that you check that there is a '.' in the name first:

 select (case when FilePath like '%.%'
              then reverse(left(reverse(FilePath), charindex('.', reverse(FilePath)) - 1))
              else ''
         end) as Extension

Answer 2

SELECT CASE 
         WHEN filepath LIKE '%.%' THEN RIGHT(filepath, Len(filepath) - 
                                                       Charindex('.', filepath)) 
         ELSE filepath 
       END FilePath 
FROM   tbl1 

Demo