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) — Evan Carroll, Nov 18 '17 at 16:23

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

82

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,242,037
58
646
786

@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 6 · Accepted Answer · answered May 28 '13 at 02:47

6

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

33,827
13
85
121

11

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
2

It's a tacit requirement. – Denziloe Sep 05 '18 at 14:49
1

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 2 · 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 1 · Answer 5 · answered Feb 03 '22 at 13:08

Its too late to answer but i found the solution how can get extension of an image or file from sql column and also get any string after any keyword or "/"or ".".... just replace the "." with your keyword.

    SELECT SUBSTR("string_or_colname",(SELECT POSITION("." IN"string_or_colname")));

    # and if you wanted to get from starting then add 0 after 
    string_or_colname,then postion
    eg :
    SELECT SUBSTR("string_or_colname",1,(SELECT POSITION("." IN"string_or_colname")));

David · Answer 6 · 2023-04-06T14:13:40.253

This will work with file names and urls (without querystrings).

If you want to do it quick and dirty inline:

SELECT REVERSE(LEFT(REVERSE([<ColumnName,,>]),CHARINDEX('.',REVERSE([<ColumnName,,>])))) AS Extension 
  FROM [<TableName,,>]

I recommend creating a scalar function. That way you can add more business logic to handle query strings or what have you.

To create a scalar function:

-- ========================================================================
-- Description: Gets extension from filename or url without a querystring
-- ========================================================================
CREATE FUNCTION get_extension 
(
    @column varchar(MAX)
)
RETURNS varchar(MAX)
AS
BEGIN
    DECLARE @Result varchar(MAX)
    SELECT @Result = REVERSE(LEFT(REVERSE(@column),CHARINDEX('.',REVERSE(@column))))
    RETURN @Result
END
GO

and then your query would simply look like this

SELECT dbo.get_extension([<ColumnName,,>]) AS Extension FROM [<TableName,,>]

As a side note, if you are using SSMS, you can press CNTL-M to populate the <Something,,> variables

score -2 · Answer 7 · 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

7 Answers7