T-Sql Get File Extension Name from a Column

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

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 

You can get file extension in this way:

select right(' charindex('.', reverse(') + '.') - 1)

And You can get file name in this way:

select right(' charindex('/', reverse(') + '/') - 1)

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")));

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

  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