18

I want to get a substring in SQL Server from last sequence of a split on dot (.).

I have a column which contains file names such as hello.exe, and I want to find the extension of the file exactly as Path.GetExtension("filename") does in C#.

Michael
  • 8,362
  • 6
  • 61
  • 88
Rajesh
  • 6,269
  • 5
  • 28
  • 23

5 Answers5

30

You can use reverse along with substring and charindex to get what you're looking for:

select
    reverse(substring(reverse(filename), 1, 
        charindex('.', reverse(filename))-1)) as FileExt
from
    mytable

This holds up, even if you have multiple . in your file (e.g.-hello.world.exe will return exe).

So I was playing around a bit with this, and this is another way (only one call to reverse):

select 
    SUBSTRING(filename, 
        LEN(filename)-(CHARINDEX('.', reverse(filename))-2), 8000) as FileExt
from
    mytable

This calculates 10,000,000 rows in 25 seconds versus 29 seconds for the former method.

Eric
  • 92,005
  • 12
  • 114
  • 115
  • 2
    +1 good call ! I was wondering if there way any easy way to deal with multiple '.' in the file name, without resorting to messy character counting and parsing - well done! – marc_s Aug 04 '09 at 10:53
  • 2
    charindex('.', reverse(filename))-1 fails when there is no dot in filename. used charindex('.', reverse(filename)) – Rajesh Aug 05 '09 at 11:23
  • 1
    in Mysql you can use SUBSTRING_INDEX, for example: SUBSTRING_INDEX(file_name, '.', -1), maybe there is something similar to sql-server – Alessandro De Simone Aug 23 '12 at 16:46
2
DECLARE @originalstring VARCHAR(100)
SET @originalstring = 'hello.exe'

DECLARE @extension VARCHAR(50)

SET @extension = SUBSTRING(@originalstring, CHARINDEX('.', @originalstring) + 1, 999)

SELECT @extension

That should do it, I hope! This works as long as you only have a single '.' in your file name - separating the file name from the extension.

Marc

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 1
    This is a good answer IF you are 100% sure there is only one period (Fullstop) in the originalstring variable. Otherwise, the approved is better. – Phil Feb 01 '12 at 22:32
2

Try this

SELECT RIGHT(
             'C:\SomeRandomFile\Filename.dat',
             CHARINDEX(
                       '.',
                       REVERSE(
                               'C:\SomeRandomFile\Filename.dat'
                              ),
                       0)
              -1)
Nilesh
  • 20,521
  • 16
  • 92
  • 148
Hss
  • 21
  • 1
1

Same as accepted answer, but I've added a condition to avoid error when filename is null or when filename has no extension (no point):

select
    reverse(substring(reverse(filename), 1, 
        charindex('.', reverse(filename))-1)) as FileExt
from
    mytable
where 
    filename is not null
and charindex('.',filename) > 0
Sylvain Rodrigue
  • 4,751
  • 5
  • 53
  • 67
0

The following SQL request adressed most of the edge cases in my weird database where many files didn't have extensions.

select distinct reverse(left(reverse(fileNameWithExtension), charindex('.', reverse(fileNameWithExtension)) - 1))
from myTable
where charindex('.', reverse(fileNameWithExtension)) - 1 > 0 and charindex('.', reverse(fileNameWithExtension)) - 1 < 7 and fileNameWithExtension is not null
Michael Fayad
  • 1,216
  • 1
  • 17
  • 38