SQL Server – Transact SQL – Get File’s Extension

Background

Needed a quick and fairly safe way to get a file’s extension from the file’s name.

Looked on the Net, found a good jump off, but it a stored procedure, and really wanted a scaler function.

 

Code

Original

Author :- Md. Marufuzzaman
Topic   :- An Easy Way to Get a File Name or a File Extension from a User Defined File Path using Transact-SQL
Dated  :- 9 Aug 2009
Link

 

dbo.udfn_getFileExt


use [master]
go
 
IF OBJECT_ID (N'dbo.udfn_getFileExt', N'FN') IS NULL 
begin
 
 
    exec('create FUNCTION dbo.udfn_getFileExt
            (
                @filename sysname
            )  
            RETURNS int  
            WITH EXECUTE AS CALLER  
            AS  
            BEGIN  
                return 1/0
            end
        ')
end
GO  
 
ALTER FUNCTION dbo.udfn_getFileExt
(
    @filename sysname
)  
RETURNS sysname
WITH EXECUTE AS CALLER  
AS 
BEGIN 
 
    declare @fileExt        sysname
    DECLARE @FILE_LENGTH    INT
    DECLARE @CHAR_PERIOD    char(1)
 
    declare @iPosOfPeriod   int
    declare @iPosOfPeriodSaved   int
    declare @fileExtLength  int
	declare @iFindFirstPeriod int

	declare @iNumberofAttempts int
	declare @iMaxNumberofAttempts int

	declare @POSITION_START int

	declare @result varchar(60)
	
	set @iNumberofAttempts = 0
	set @iMaxNumberofAttempts = 20
 
    set @CHAR_PERIOD = '.'
	set @POSITION_START = 1
	set @iPosOfPeriod = @POSITION_START
	set @iPosOfPeriodSaved = @iPosOfPeriod 
 
    if
        (
               (@filename is null)
            or (ltrim(rtrim(@filename)) = '')
        )
    begin
 
        return null
 
    end
          
    /*
        Get length of file name
    */
    SET @FILE_LENGTH = LEN(@filename)

    /*
        Get Position of first period
    */
	set @iFindFirstPeriod =
			  charindex
			  (
				  @CHAR_PERIOD
				, @filename
			  )
 
    /*
        If File's name is not empty
			and period found
    */
    if (
			    (@FILE_LENGTH <> 0)
			and (@iFindFirstPeriod > 0)
		)
    BEGIN
 
        /*
            Get last position of period
        */
		while (
					    @iPosOfPeriod != 0
					and @iNumberofAttempts <= @iMaxNumberofAttempts
			  )
		begin			  	

			set @iNumberofAttempts = @iNumberofAttempts + 1

			set @iPosOfPeriod = charindex
								  (
									  @CHAR_PERIOD
									, @filename
									, @iPosOfPeriodSaved+1
								  )

			if (@iPosOfPeriod != 0)
			begin

				set @iPosOfPeriodSaved = @iPosOfPeriod

			end

		end
		 
        if (@iPosOfPeriodSaved > 0)
        BEGIN
 
            set @fileExtLength = @FILE_LENGTH
                                     - @iPosOfPeriodSaved
                                     + 1
 
            SET @fileExt = substring
                            (
                                  @filename
                                , @iPosOfPeriodSaved+1
                                , @fileExtLength
                            )
 
       END
 
     END
     

	set @result = @fileExt

	return (@result)


 
END;  
GO 
 
grant execute on [dbo].[udfn_getFileExt] to [public]
go

Sample


declare @filename	sysname
declare @fileExt	sysname
declare @CHAR_TAB	char(1)

set @CHAR_TAB = char(9)
set @filename = 'D:\Temp\Testfile.txt'

set @fileExt = [master].[dbo].[udfn_getFileExt]
				  (@filename)

print '@filename: ' + @CHAR_TAB + isNull(@filename, '')
print '@fileExt: '  + @CHAR_TAB + isNull(@fileExt, '')

 

Output

filenameoutput

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s