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?

possible duplicate of [Find index of last occurrence of a sub-string using T-SQL](http://stackoverflow.com/questions/1024978/find-index-of-last-occurrence-of-a-sub-string-using-t-sql) — Jim G., May 28 '13 at 02:40
@Joel Coehoorn sorry, i mean new colum in a new table, just as the code pasted above. The problem is solved. thanks anyway. — Edi Wang, May 28 '13 at 03:09
Possible duplicate of [Remove extensions from filename](https://stackoverflow.com/questions/11241635/remove-extensions-from-filename) — user157251, Nov 18 '17 at 16:23

Gordon Linoff · Answer 1 · 2013-05-28T02:44:11.293

69

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

edited May 28 '13 at 02:44

answered May 28 '13 at 02:37

Gordon Linoff

1,122,135
50
484
624

@FreshPrinceOfSO . . . The original *did* work on my test example, but I fixed it. – Gordon Linoff May 28 '13 at 02:46
1

@Abhishek . . . The second query works in that case, as explicitly stated in the answer. – Gordon Linoff Dec 07 '15 at 13:15

score 7 · Accepted Answer · answered May 28 '13 at 02:47

7

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

Demo

answered May 28 '13 at 02:47

Kermit

32,563
10
80
117

9

Doesn't handle files with multiple "." in name. – JonK Nov 15 '13 at 17:04
2

Wasn't part of the requirement. – Kermit Nov 15 '13 at 17:10
4

this one doesn't handle all cases. Gordon Linoff answer works better for me – Sergey Apr 25 '16 at 15:20
1

It's a tacit requirement. – Denziloe Sep 05 '18 at 14:49
File extension is the part of the string after the **last** period. The question was how to get the file extension so it was part of the requirement... – tibx Mar 30 '21 at 15:24

score 2 · Answer 3 · answered Feb 09 '17 at 10:47

When looking for specific extensions, and dealing with texts that do have many dots, the following provides a more specific approach.

with extensionTable ([extension]) AS
(
    SELECT '.pdf'
    UNION ALL SELECT '.txt'
    UNION ALL SELECT '.doc'
    UNION ALL SELECT '.docx'
    UNION ALL SELECT '.xls'
    UNION ALL SELECT '.xlsx'
    UNION ALL SELECT '.ppt'
    UNION ALL SELECT '.pptx'
    UNION ALL SELECT '.zip'
    UNION ALL SELECT '.tar.gz'
    UNION ALL SELECT '.htm'
    UNION ALL SELECT '.html'
)

SELECT T2.[extension]
        , T1.[document_file_name]
FROM tbl T1
OUTER APPLY ( SELECT TOP 1 [extension] 
                FROM extensionTable
                WHERE CHARINDEX([extension], T1.[document_file_name]) > 0
                ORDER BY CHARINDEX([extension], T1.[document_file_name]) DESC, LEN([extension]) DESC
            ) T2

score 0 · Answer 4 · answered Nov 04 '19 at 08:16

You can get file extension in this way:

select right('https://google.com/1.png', charindex('.', reverse('https://google.com/1.png') + '.') - 1)

And You can get file name in this way:

select right('https://google.com/1.png', charindex('/', reverse('https://google.com/1.png') + '/') - 1)

score -2 · Answer 5 · edited Sep 21 '19 at 16:08

  declare @filepath char(250) = 'c:\powersql\database\teste.txtdat'
  Declare @NewExtesion Char(15) = 'Old' 



--  @filepath char(250)                           = 'c:\powersql\database\teste.txtdat'
--  select len(@FilePath)                         = 33
--  Select Charindex('.', @filepath)              = Len of Filepath before Extension  
--  select RIGHT(rtrim(@filepath) ,len(@FilePath) - Charindex('.', @filepath) )   = txtdat (extension i would like to change)  
--  select REPLACE(@filepath, RIGHT(rtrim(@filepath) ,len(@FilePath) - Charindex('.', @filepath)), 'xxx')
       -- here i changed .txdat to xxx

-- below the full query 

SELECT 
      CASE 
         WHEN @filepath LIKE '%.%' 
               THEN  REPLACE(@filepath, RIGHT(rtrim(@filepath) ,len(@FilePath) - Charindex('.', @filepath)), @NewExtesion)
         ELSE @filepath 
       END

This is not what was asked for. You should just extract extension. Even taking just the ```RIGHT(...)``` will not work always (because you cannot say for sure, that there is always one ```.``` in the path). This is why the question said "But T-SQL doesn't have a ```LastIndexOf()``` function." But you might have a try to correct your answer making use of the ```REVERSE()``` function. — Islingre, Sep 21 '19 at 14:17

T-SQL Get File Extension Name from a Column

5 Answers5