File Name Parsing Calculations - SQL Server

Overview¶

Given a database table with a column containing a fully-qualified file name (full path + file name + file extension), we want to parse out the file extension and the file name without the extension.

Solution¶

The following code assumes the table is called ImageFile and the fully-qualified file name is in the FullFileName column. It requires that you have two auxiliary INT columns: LastSlashPos and LastDotPos.

PITFALL: This code assumes that the only period is for the file extension — i.e., that none of the folders in the file's path contain a period.

update 
    dbo.ImageFile
set
     LastSlashPos = len(FullFileName) - charindex('\', reverse(FullFileName)) + 1
    ,LastDotPos   = len(FullFileName) - charindex('.', reverse(FullFileName)) + 1
-----------------------------
update 
    dbo.ImageFile
set
     FileExtension      = substring(FullFileName, LastDotPos+1,Len(FullFileName))
     FileNameWithoutExt = substring(FullFileName, LastSlashPos+1, LastDotPos-LastSlashPos-1)

User-Defined Function¶

The following function returns the file extension (including the leading period) of the file specified.

/*
select result = dbo.udsFileExtension('hello.vbs.txt.xml')
*/
ALTER function [dbo].[udsFileExtension]
    (
    @file varchar(max)
    ) returns varchar(max) as begin

declare 
     @result varchar(max)
    ,@pos int

select 
     @pos       = len(@file) - charindex('.', reverse(@file)) + 1
    ,@result    = case 
                    when @file like '%.%' then substring(@file, @pos, len(@file)) 
                    else '' 
                    end

return @result

end