SQL Server – Transact SQL – Get File’s Folder name

Code



use [master]
go

IF OBJECT_ID (N'dbo.udfn_getFolderName', N'FN') IS NULL  
begin


	exec('create FUNCTION dbo.udfn_getFolderName
			(
				@filename sysname
			)  
			RETURNS int  
			WITH EXECUTE AS CALLER  
			AS  
			BEGIN  
				return 1/0
			end
		')
end
GO  

ALTER FUNCTION [dbo].[udfn_getFolderName]
(
	@filename sysname
)  
RETURNS sysname
WITH EXECUTE AS CALLER  
AS  
BEGIN  


	declare @foldername			sysname

	DECLARE @FILE_LENGTH		INT

	DECLARE @CHAR_PERIOD		char(1)
	DECLARE @CHAR_BACKSLASH		char(1)
	DECLARE @CHAR_TAB			char(1)

	declare @iPosOfBackSlashSaved int
	declare @iPosOfBackSlash	  int

	declare @iAttempts		 int
	declare @MAX_NUMBER_OF_ATTEMPTS	 int

	declare @POS_BACKSLASH_NOTFOUND int
	declare @POS_BACKSLASH_INITIAL int

	set @CHAR_PERIOD = '.'
	set @CHAR_BACKSLASH = '\'
	set @CHAR_TAB = char(9)

	set @POS_BACKSLASH_NOTFOUND = 0
	set @POS_BACKSLASH_INITIAL = 1

	set @MAX_NUMBER_OF_ATTEMPTS = 16

	if
		(
			   (@filename is null)
			or (ltrim(rtrim(@filename)) = '')
		)
	begin

		return null

	end
		 
	/*
		Get length of file name
	*/
	SET @FILE_LENGTH = LEN(@filename)

	if (@FILE_LENGTH = 0)
	BEGIN
		return null
	end

	set @iPosOfBackSlashSaved = @POS_BACKSLASH_INITIAL
	set @iPosOfBackSlash = @POS_BACKSLASH_INITIAL
	set @iAttempts = 1

	/*
		Loop while
				1)
					bootstrapping - Blackslash @ position 1
					Blackslash still being found

				2) Attempts still within loop
	*/
	while (
			
	
		     (
				   (@iPosOfBackSlash = @POS_BACKSLASH_INITIAL)
				or (@iPosOfBackSlash != @POS_BACKSLASH_NOTFOUND)
			)
			and  (@iAttempts < @MAX_NUMBER_OF_ATTEMPTS)
		  )
	begin

		/*
			increment attempts
		*/
		set @iAttempts = @iAttempts + 1

		if (@iPosOfBackSlashSaved != @iPosOfBackSlash)
		begin

			set @iPosOfBackSlashSaved = @iPosOfBackSlash

		end

		/*
			Get position of BackSlash
		*/
		set @iPosOfBackSlash
			 = charindex
			  (
				  @CHAR_BACKSLASH
				, @filename
				, @iPosOfBackSlashSaved + 1
			  )

	end

	/*
		If BackSlash marker not found in file name, then exit
	*/
	if (@iPosOfBackSlashSaved = 0)
	begin

		return null

	end

	/*
		Get base file name and extension
	*/
	set @foldername
			= substring
				  (
					  @filename
					, 1
					, @iPosOfBackSlashSaved
				  )	

	return @foldername

END;  
GO 

grant execute on [dbo].[udfn_getFolderName] to [public]
go

Lab

Sample Code


declare @filename	sysname
declare @fileExt	sysname
declare @CHAR_TAB	char(1)
	
set @CHAR_TAB = char(9)
set @filename = 'D:\Temp\SQLServer\Import\Testfile.txt'
 
set @fileExt = [master].[dbo].[udfn_getFolderName]
				  (@filename)

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

Output

foldernameoutput

 

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