Get extension of a file using SQL?

Question

Possible Duplicate:
Get substring in SQL Server

Let's say I have MyImage.png or MyDoc.doc, etc in a column in a database table.

How can I only get the file extension?

Select substring LIKE with a regexp :-) and extract – bonCodigo Nov 22 '12 at 08:19 — bonCodigo, Nov 22 '12 at 08:19

score 50 · Accepted Answer · edited Oct 04 '22 at 18:52

50

try this:

declare @str varchar(20)='MyDoc.doc';
select reverse(left(reverse(@str),charindex('.',reverse(@str))-1))

###SQL fiddle demo

edited Oct 04 '22 at 18:52

luiscla27

4,956
37
49

answered Nov 22 '12 at 08:18

Joe G Joseph

23,518
5
56
58

4

Fails if @str does not contain a '.' – Todd Smith Apr 15 '14 at 11:31
2

Good answer, but not supporting stripping away querysrings, like "index.html?id=1". But besides that, good answer :-) – Mats Magnem Feb 04 '15 at 19:03
1

Add a check to check if '.' is present in the filename else return the filename itself `declare @str varchar(20)='MyDocdoc'; select CASE WHEN charindex('.',@str) > 0 THEN reverse(left(reverse(@str),charindex('.',reverse(@str))-1)) ELSE @str END` – Idris Jun 09 '21 at 11:47

score 14 · Answer 2 · edited Oct 04 '22 at 18:51

14

Try this:

SELECT parsename(filename,1) FROM yourTable

sqlfiddle demo

edited Oct 04 '22 at 18:51

luiscla27

4,956
37
49

answered Nov 22 '12 at 08:27

praveen

12,083
1
41
49

1

A period is valid in a file name. This won't work if your file name has more than 3 periods. This.File.Name.Will.Break.txt – codeConcussion Jan 12 '15 at 23:41
But it works if there is no more than two periods in file name. `This.Will.Work.txt`. – Vadzim Feb 05 '16 at 17:35

score 5 · Answer 3 · edited Oct 04 '22 at 18:51

5

With string functions:

SELECT Extension =
   Right([Name],
        CHARINDEX('.',
                  REVERSE([Name]))-1)
FROM dbo.Files

SQL fiddle demo

edited Oct 04 '22 at 18:51

luiscla27

4,956
37
49

answered Nov 22 '12 at 08:28

Tim Schmelter

450,073
74
686
939

6

This breaks on filenames without extension: `Invalid length parameter passed to the right function.` – Nickolay Jan 02 '18 at 16:06

score 4 · Answer 4 · edited Oct 04 '22 at 18:50

4

SELECT RIGHT('myFile.txt', 
               CHARINDEX('.', REVERSE('myFile.txt'))-1) AS 'File Extension'

Documentation: SUBSTRING, RIGHT, CHARINDEX, REVERSE

edited Oct 04 '22 at 18:50

luiscla27

4,956
37
49

answered Nov 22 '12 at 08:23

CloudyMarble

36,908
70
97
130

It seems the last edit is a dup of [the answer](https://stackoverflow.com/a/13508538/1657465) Tim gave – luiscla27 Oct 04 '22 at 20:33

score 3 · Answer 5 · edited Oct 04 '22 at 18:52

3

declare @str varchar(20)='MyDoc.doc'
select reverse(left(reverse(@str),CHARINDEX('.',reverse(@str))-1))

FIDDLE DEMO

edited Oct 04 '22 at 18:52

luiscla27

4,956
37
49

answered Nov 22 '12 at 08:19

AnandPhadke

13,160
5
26
33

i updated the query pls check – AnandPhadke Nov 22 '12 at 08:22
Failing, declare @str varchar(20)='2012_11_22 11_25_47_260.jpg'; select RIGHT(@str,(len(@str)-CHARINDEX('.',@str))) – Imran Qadir Baksh - Baloch Nov 22 '12 at 08:27
NOt working, Please test it throughly – Imran Qadir Baksh - Baloch Nov 22 '12 at 08:29
2

try the fiddle demo its working – AnandPhadke Nov 22 '12 at 08:30
this ended up a complete duplicate of the accepted answer: https://stackoverflow.com/a/13508412/1026 – Nickolay Jan 02 '18 at 16:03

Get extension of a file using SQL?

5 Answers5

Linked